{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "helper load finish!!!\n"
     ]
    }
   ],
   "source": [
    "import warnings\n",
    "warnings.filterwarnings('ignore')\n",
    "from base_helper import *\n",
    "train_tst = get_transaction_train_new()\n",
    "test_tst_rd = get_transaction_round1_new()\n",
    "\n",
    "tag = get_tag_train_new()\n",
    "test_tst_rd[tag_hd.Tag] = -1\n",
    "train_tst = pd.merge(train_tst, tag, on='UID', how='left')\n",
    "\n",
    "'''合并训练测试集'''\n",
    "train_tsts = pd.concat([train_tst, test_tst_rd], axis=0).reset_index(drop=True)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# 数据异常的清除,删除channel为118的"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "train_tsts = train_tsts[train_tsts.channel != 118].reset_index(drop=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array([102, 140, 119, 106], dtype=int64)"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "train_tsts.channel.unique()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "(60561, 1) (424377, 28)\n",
      "(60561, 2) (424377, 28)\n"
     ]
    }
   ],
   "source": [
    "train_tst_feature = pd.DataFrame({'UID':[i for i in train_tsts.UID.unique()]})\n",
    "print(train_tst_feature.shape, train_tsts.shape)\n",
    "train_tst_feature = train_tst_feature.merge(train_tsts.drop_duplicates(['UID', 'Tag'])[['UID', 'Tag']],\n",
    "                                            on='UID',how='left')\n",
    "print(train_tst_feature.shape, train_tsts.shape)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# channel是否为119"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [],
   "source": [
    "def fucs_channel(x):\n",
    "    temp = x.values.tolist()\n",
    "    if (119 in temp):\n",
    "        return 1\n",
    "    else:\n",
    "        return 0\n",
    "train_channel = train_tsts.groupby('UID')['channel'].apply(fucs_channel).reset_index().rename(\n",
    "    {\"channel\":'channel_119'}, axis=1)\n",
    "train_tst_feature = train_tst_feature.merge(train_channel, on='UID', how='left')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# trans_amt, bal的处理"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [],
   "source": [
    "'''交易金额是否大于3000000'''\n",
    "def fucs_trans_amt(x):\n",
    "    temp = np.max(x.values.tolist())\n",
    "    if temp > 3000000:\n",
    "        return 1\n",
    "    else:\n",
    "        return 0\n",
    "train_trans_amt = train_tsts.groupby('UID')['trans_amt'].apply(fucs_trans_amt).reset_index().rename(\n",
    "    {\"trans_amt\":'trans_amt_than_119'}, axis=1)\n",
    "\n",
    "def fucs_trans_amt_102(x):\n",
    "    temp = x.values.tolist()\n",
    "    if 102 in temp:\n",
    "        return 1\n",
    "    else:\n",
    "        return 0\n",
    "train_trans_amt_102 = train_tsts.groupby('UID')['trans_amt'].apply(fucs_trans_amt_102).reset_index().rename(\n",
    "    {\"trans_amt\":'trans_amt_102'}, axis=1)\n",
    "\n",
    "train_tst_feature = train_tst_feature.merge(train_trans_amt, on='UID', how='left')\n",
    "train_tst_feature = train_tst_feature.merge(train_trans_amt_102, on='UID', how='left')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [],
   "source": [
    "def fucs_bal_big(x):\n",
    "    temp = np.max(x.values.tolist())\n",
    "    if temp > 3200000:\n",
    "        return 1\n",
    "    else:\n",
    "        return 0\n",
    "train_bal_big = train_tsts.groupby('UID')['bal'].apply(fucs_bal_big).reset_index().rename(\n",
    "    {\"bal\":'trans_bal_big'}, axis=1)\n",
    "\n",
    "def fucs_bal_100(x):\n",
    "    temp = x.values.tolist()\n",
    "    if 100 in temp:\n",
    "        return 1\n",
    "    else:\n",
    "        return 0\n",
    "train_bal_100 = train_tsts.groupby('UID')['bal'].apply(fucs_bal_100).reset_index().rename(\n",
    "    {\"bal\":'bal_100'}, axis=1)\n",
    "\n",
    "def fucs_bal_100_260(x):\n",
    "    temp = x.values.tolist()\n",
    "    count = 0\n",
    "    for i in temp:\n",
    "        if i>100 & i < 260:\n",
    "            count += 1\n",
    "    ratio = count / len(temp)\n",
    "    return ratio\n",
    "train_bal_100_260 = train_tsts.groupby('UID')['bal'].apply(fucs_bal_100_260).reset_index().rename(\n",
    "    {\"bal\":'bal_100_260'}, axis=1)\n",
    "\n",
    "\n",
    "train_tst_feature = train_tst_feature.merge(train_bal_big, on='UID', how='left')\n",
    "train_tst_feature = train_tst_feature.merge(train_bal_100, on='UID', how='left')\n",
    "train_tst_feature = train_tst_feature.merge(train_bal_100_260, on='UID', how='left')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# 是不是1，8以及14，12，以及26，27，28"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "def fucs_1(x):\n",
    "    temp = x.values.tolist()\n",
    "    if (1 in temp):\n",
    "        return 1\n",
    "    else:\n",
    "        return 0\n",
    "def fucs_8(x):\n",
    "    temp = x.values.tolist()\n",
    "    if (8 in temp):\n",
    "        return 1\n",
    "    else:\n",
    "        return 0\n",
    "def fucs_12(x):\n",
    "    temp = x.values.tolist()\n",
    "    if (12 in temp):\n",
    "        return 1\n",
    "    else:\n",
    "        return 0\n",
    "def fucs_14(x):\n",
    "    temp = x.values.tolist()\n",
    "    if (14 in temp):\n",
    "        return 1\n",
    "    else:\n",
    "        return 0\n",
    "def fucs_26(x):\n",
    "    temp = x.values.tolist()\n",
    "    if (26 in temp):\n",
    "        return 1\n",
    "    else:\n",
    "        return 0\n",
    "def fucs_27(x):\n",
    "    temp = x.values.tolist()\n",
    "    if (27 in temp):\n",
    "        return 1\n",
    "    else:\n",
    "        return 0\n",
    "def fucs_28(x):\n",
    "    temp = x.values.tolist()\n",
    "    if (28 in temp):\n",
    "        return 1\n",
    "    else:\n",
    "        return 0\n",
    "day_1 = train_tsts.groupby('UID')['day'].apply(fucs_1).reset_index().rename({\"day\":'day_is_1'}, axis=1)\n",
    "day_8 = train_tsts.groupby('UID')['day'].apply(fucs_8).reset_index().rename({\"day\":'day_is_8'}, axis=1)\n",
    "day_12= train_tsts.groupby('UID')['day'].apply(fucs_12).reset_index().rename({\"day\":'day_is_12'}, axis=1)\n",
    "day_14= train_tsts.groupby('UID')['day'].apply(fucs_14).reset_index().rename({\"day\":'day_is_14'}, axis=1)\n",
    "day_26= train_tsts.groupby('UID')['day'].apply(fucs_26).reset_index().rename({\"day\":'day_is_26'}, axis=1)\n",
    "day_27= train_tsts.groupby('UID')['day'].apply(fucs_27).reset_index().rename({\"day\":'day_is_27'}, axis=1)\n",
    "day_28= train_tsts.groupby('UID')['day'].apply(fucs_28).reset_index().rename({\"day\":'day_is_28'}, axis=1)\n",
    "train_tst_feature = train_tst_feature.merge(day_1, on='UID', how='left')\n",
    "train_tst_feature = train_tst_feature.merge(day_8, on='UID', how='left')\n",
    "train_tst_feature = train_tst_feature.merge(day_12, on='UID', how='left')\n",
    "train_tst_feature = train_tst_feature.merge(day_14, on='UID', how='left')\n",
    "train_tst_feature = train_tst_feature.merge(day_26, on='UID', how='left')\n",
    "train_tst_feature = train_tst_feature.merge(day_27, on='UID', how='left')\n",
    "train_tst_feature = train_tst_feature.merge(day_28, on='UID', how='left')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>UID</th>\n",
       "      <th>Tag</th>\n",
       "      <th>channel_119</th>\n",
       "      <th>trans_amt_than_119</th>\n",
       "      <th>trans_amt_102</th>\n",
       "      <th>trans_bal_big</th>\n",
       "      <th>bal_100</th>\n",
       "      <th>bal_100_260</th>\n",
       "      <th>day_is_1</th>\n",
       "      <th>day_is_8</th>\n",
       "      <th>day_is_12</th>\n",
       "      <th>day_is_14</th>\n",
       "      <th>day_is_26</th>\n",
       "      <th>day_is_27</th>\n",
       "      <th>day_is_28</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>19092</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0.105263</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>13465</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0.105263</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>13713</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0.507216</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>22703</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>17816</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0.285714</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>12037</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>18983</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0.181818</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>12741</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>15986</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>10057</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0.285714</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>18023</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0.055556</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>10953</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>20331</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>15008</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0.428571</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>12983</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0.272727</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>21206</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0.419355</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>16882</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>11298</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>14873</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0.222222</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>21509</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0.049020</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20</th>\n",
       "      <td>17520</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0.247891</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21</th>\n",
       "      <td>17259</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0.171429</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>22</th>\n",
       "      <td>13426</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0.019048</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23</th>\n",
       "      <td>16146</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>24</th>\n",
       "      <td>17856</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25</th>\n",
       "      <td>20280</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>26</th>\n",
       "      <td>21856</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>27</th>\n",
       "      <td>11103</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0.125000</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>28</th>\n",
       "      <td>15283</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0.528302</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>29</th>\n",
       "      <td>11981</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0.190476</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>60531</th>\n",
       "      <td>85200</td>\n",
       "      <td>-1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>60532</th>\n",
       "      <td>75567</td>\n",
       "      <td>-1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>60533</th>\n",
       "      <td>83320</td>\n",
       "      <td>-1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>60534</th>\n",
       "      <td>76527</td>\n",
       "      <td>-1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>60535</th>\n",
       "      <td>82608</td>\n",
       "      <td>-1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>60536</th>\n",
       "      <td>79526</td>\n",
       "      <td>-1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>60537</th>\n",
       "      <td>75657</td>\n",
       "      <td>-1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>60538</th>\n",
       "      <td>85266</td>\n",
       "      <td>-1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>60539</th>\n",
       "      <td>73255</td>\n",
       "      <td>-1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>60540</th>\n",
       "      <td>83823</td>\n",
       "      <td>-1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>60541</th>\n",
       "      <td>85679</td>\n",
       "      <td>-1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>60542</th>\n",
       "      <td>78818</td>\n",
       "      <td>-1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>60543</th>\n",
       "      <td>80350</td>\n",
       "      <td>-1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>60544</th>\n",
       "      <td>72559</td>\n",
       "      <td>-1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>60545</th>\n",
       "      <td>73214</td>\n",
       "      <td>-1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>60546</th>\n",
       "      <td>79495</td>\n",
       "      <td>-1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>60547</th>\n",
       "      <td>70150</td>\n",
       "      <td>-1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>60548</th>\n",
       "      <td>84041</td>\n",
       "      <td>-1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>60549</th>\n",
       "      <td>76112</td>\n",
       "      <td>-1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>60550</th>\n",
       "      <td>73980</td>\n",
       "      <td>-1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>60551</th>\n",
       "      <td>86450</td>\n",
       "      <td>-1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>60552</th>\n",
       "      <td>73382</td>\n",
       "      <td>-1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>60553</th>\n",
       "      <td>83678</td>\n",
       "      <td>-1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>60554</th>\n",
       "      <td>86034</td>\n",
       "      <td>-1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>60555</th>\n",
       "      <td>81029</td>\n",
       "      <td>-1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>60556</th>\n",
       "      <td>82688</td>\n",
       "      <td>-1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>60557</th>\n",
       "      <td>75056</td>\n",
       "      <td>-1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>60558</th>\n",
       "      <td>77404</td>\n",
       "      <td>-1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>60559</th>\n",
       "      <td>79807</td>\n",
       "      <td>-1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>60560</th>\n",
       "      <td>84431</td>\n",
       "      <td>-1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>60561 rows × 15 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "         UID  Tag  channel_119  trans_amt_than_119  trans_amt_102  \\\n",
       "0      19092    0            0                   0              0   \n",
       "1      13465    0            0                   0              0   \n",
       "2      13713    0            0                   0              1   \n",
       "3      22703    0            0                   0              0   \n",
       "4      17816    0            0                   0              0   \n",
       "5      12037    0            0                   0              0   \n",
       "6      18983    0            0                   0              0   \n",
       "7      12741    0            0                   0              0   \n",
       "8      15986    0            0                   0              0   \n",
       "9      10057    0            0                   0              1   \n",
       "10     18023    0            0                   0              0   \n",
       "11     10953    0            0                   0              0   \n",
       "12     20331    0            0                   0              0   \n",
       "13     15008    0            0                   0              0   \n",
       "14     12983    0            0                   0              0   \n",
       "15     21206    0            0                   0              1   \n",
       "16     16882    0            0                   0              0   \n",
       "17     11298    0            0                   0              0   \n",
       "18     14873    0            0                   0              0   \n",
       "19     21509    0            0                   0              0   \n",
       "20     17520    0            0                   0              1   \n",
       "21     17259    0            0                   1              1   \n",
       "22     13426    0            0                   0              0   \n",
       "23     16146    0            0                   0              0   \n",
       "24     17856    1            0                   0              0   \n",
       "25     20280    0            1                   0              0   \n",
       "26     21856    0            0                   0              0   \n",
       "27     11103    0            1                   0              1   \n",
       "28     15283    0            0                   0              0   \n",
       "29     11981    0            1                   0              0   \n",
       "...      ...  ...          ...                 ...            ...   \n",
       "60531  85200   -1            0                   0              0   \n",
       "60532  75567   -1            0                   0              0   \n",
       "60533  83320   -1            0                   0              0   \n",
       "60534  76527   -1            0                   0              0   \n",
       "60535  82608   -1            0                   0              0   \n",
       "60536  79526   -1            0                   0              0   \n",
       "60537  75657   -1            0                   0              0   \n",
       "60538  85266   -1            0                   0              0   \n",
       "60539  73255   -1            0                   0              0   \n",
       "60540  83823   -1            0                   0              0   \n",
       "60541  85679   -1            0                   0              0   \n",
       "60542  78818   -1            0                   0              0   \n",
       "60543  80350   -1            0                   0              0   \n",
       "60544  72559   -1            0                   0              0   \n",
       "60545  73214   -1            0                   0              0   \n",
       "60546  79495   -1            0                   0              0   \n",
       "60547  70150   -1            0                   0              0   \n",
       "60548  84041   -1            0                   0              0   \n",
       "60549  76112   -1            0                   0              0   \n",
       "60550  73980   -1            0                   0              0   \n",
       "60551  86450   -1            0                   0              0   \n",
       "60552  73382   -1            0                   0              0   \n",
       "60553  83678   -1            0                   0              0   \n",
       "60554  86034   -1            0                   0              0   \n",
       "60555  81029   -1            0                   0              0   \n",
       "60556  82688   -1            0                   0              0   \n",
       "60557  75056   -1            0                   0              0   \n",
       "60558  77404   -1            0                   0              0   \n",
       "60559  79807   -1            0                   0              0   \n",
       "60560  84431   -1            0                   0              0   \n",
       "\n",
       "       trans_bal_big  bal_100  bal_100_260  day_is_1  day_is_8  day_is_12  \\\n",
       "0                  0        1     0.105263         0         0          0   \n",
       "1                  0        1     0.105263         0         0          0   \n",
       "2                  0        1     0.507216         0         1          1   \n",
       "3                  0        1     0.000000         1         0          0   \n",
       "4                  0        1     0.285714         1         1          1   \n",
       "5                  0        1     0.000000         0         1          0   \n",
       "6                  0        1     0.181818         1         1          1   \n",
       "7                  0        1     0.000000         0         0          0   \n",
       "8                  0        1     0.000000         1         0          0   \n",
       "9                  0        1     0.285714         0         0          0   \n",
       "10                 0        1     0.055556         1         1          0   \n",
       "11                 0        1     0.000000         0         0          0   \n",
       "12                 0        1     0.000000         0         0          0   \n",
       "13                 0        1     0.428571         1         0          1   \n",
       "14                 0        1     0.272727         1         1          0   \n",
       "15                 0        1     0.419355         0         1          0   \n",
       "16                 0        1     0.000000         0         0          0   \n",
       "17                 0        1     0.000000         0         0          0   \n",
       "18                 0        1     0.222222         1         0          0   \n",
       "19                 0        1     0.049020         1         1          1   \n",
       "20                 0        1     0.247891         0         0          0   \n",
       "21                 0        1     0.171429         0         0          1   \n",
       "22                 0        1     0.019048         0         0          0   \n",
       "23                 0        1     0.000000         0         0          0   \n",
       "24                 0        1     0.000000         0         0          0   \n",
       "25                 0        1     0.000000         0         0          0   \n",
       "26                 0        1     0.000000         1         0          0   \n",
       "27                 0        1     0.125000         1         1          0   \n",
       "28                 0        1     0.528302         1         0          0   \n",
       "29                 0        1     0.190476         0         0          0   \n",
       "...              ...      ...          ...       ...       ...        ...   \n",
       "60531              0        1     0.000000         0         0          0   \n",
       "60532              0        1     0.000000         0         0          0   \n",
       "60533              0        1     0.000000         0         0          0   \n",
       "60534              0        1     0.000000         0         0          0   \n",
       "60535              0        1     0.000000         0         0          0   \n",
       "60536              0        1     0.000000         0         0          0   \n",
       "60537              0        1     0.000000         0         0          0   \n",
       "60538              0        1     0.000000         0         1          0   \n",
       "60539              0        1     0.000000         0         0          0   \n",
       "60540              0        1     0.000000         0         0          0   \n",
       "60541              0        1     0.000000         0         0          0   \n",
       "60542              0        1     0.000000         0         0          0   \n",
       "60543              0        1     0.000000         0         0          0   \n",
       "60544              0        1     0.000000         0         0          0   \n",
       "60545              0        1     0.000000         0         0          0   \n",
       "60546              0        1     0.000000         0         0          0   \n",
       "60547              0        1     0.000000         0         0          0   \n",
       "60548              0        1     0.000000         0         0          0   \n",
       "60549              0        1     0.000000         0         0          0   \n",
       "60550              0        1     0.000000         0         1          0   \n",
       "60551              0        1     0.000000         0         0          0   \n",
       "60552              0        1     0.000000         0         0          0   \n",
       "60553              0        1     0.000000         0         0          0   \n",
       "60554              0        1     0.000000         0         0          0   \n",
       "60555              0        1     0.000000         0         0          0   \n",
       "60556              0        1     0.000000         0         0          0   \n",
       "60557              0        1     0.000000         0         0          0   \n",
       "60558              0        1     0.000000         0         0          0   \n",
       "60559              0        1     0.000000         0         0          0   \n",
       "60560              0        1     0.000000         0         0          0   \n",
       "\n",
       "       day_is_14  day_is_26  day_is_27  day_is_28  \n",
       "0              0          0          0          0  \n",
       "1              0          0          0          0  \n",
       "2              1          1          0          1  \n",
       "3              0          0          0          0  \n",
       "4              0          1          0          0  \n",
       "5              0          0          0          0  \n",
       "6              1          0          1          1  \n",
       "7              0          0          0          0  \n",
       "8              0          0          0          0  \n",
       "9              0          1          0          0  \n",
       "10             1          0          0          0  \n",
       "11             0          0          0          0  \n",
       "12             0          0          0          0  \n",
       "13             1          1          1          0  \n",
       "14             0          0          0          0  \n",
       "15             0          1          1          0  \n",
       "16             0          0          0          0  \n",
       "17             0          0          0          0  \n",
       "18             0          0          0          0  \n",
       "19             0          1          1          1  \n",
       "20             1          1          1          0  \n",
       "21             1          1          1          1  \n",
       "22             0          1          0          0  \n",
       "23             0          1          1          0  \n",
       "24             0          0          0          1  \n",
       "25             0          0          0          0  \n",
       "26             0          0          0          0  \n",
       "27             0          0          0          1  \n",
       "28             0          1          0          1  \n",
       "29             0          0          1          1  \n",
       "...          ...        ...        ...        ...  \n",
       "60531          0          0          0          1  \n",
       "60532          0          0          0          1  \n",
       "60533          0          0          0          0  \n",
       "60534          0          0          0          1  \n",
       "60535          0          0          0          1  \n",
       "60536          0          0          0          0  \n",
       "60537          0          0          0          0  \n",
       "60538          0          0          0          0  \n",
       "60539          0          0          0          1  \n",
       "60540          0          0          0          0  \n",
       "60541          0          0          0          1  \n",
       "60542          0          0          0          0  \n",
       "60543          0          0          0          0  \n",
       "60544          0          0          0          0  \n",
       "60545          0          0          0          0  \n",
       "60546          0          0          0          0  \n",
       "60547          0          0          0          0  \n",
       "60548          0          0          0          0  \n",
       "60549          0          0          0          1  \n",
       "60550          0          0          0          0  \n",
       "60551          0          0          0          1  \n",
       "60552          0          0          0          0  \n",
       "60553          0          1          0          0  \n",
       "60554          0          0          0          1  \n",
       "60555          0          0          0          0  \n",
       "60556          0          0          0          0  \n",
       "60557          0          0          0          0  \n",
       "60558          0          0          0          0  \n",
       "60559          0          0          0          0  \n",
       "60560          0          0          0          0  \n",
       "\n",
       "[60561 rows x 15 columns]"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "train_tst_feature"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# 交易天的个数统计,平均每天的操作次数，每个时间段的操作次数"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>UID</th>\n",
       "      <th>channel</th>\n",
       "      <th>day</th>\n",
       "      <th>time</th>\n",
       "      <th>trans_amt</th>\n",
       "      <th>amt_src1</th>\n",
       "      <th>merchant</th>\n",
       "      <th>code1</th>\n",
       "      <th>code2</th>\n",
       "      <th>trans_type1</th>\n",
       "      <th>...</th>\n",
       "      <th>bal</th>\n",
       "      <th>amt_src2</th>\n",
       "      <th>acc_id2</th>\n",
       "      <th>acc_id3</th>\n",
       "      <th>geo_code</th>\n",
       "      <th>trans_type2</th>\n",
       "      <th>market_code</th>\n",
       "      <th>market_type</th>\n",
       "      <th>ip1_sub</th>\n",
       "      <th>Tag</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>13713</td>\n",
       "      <td>140</td>\n",
       "      <td>22</td>\n",
       "      <td>09:56:39</td>\n",
       "      <td>127</td>\n",
       "      <td>c5fc631370cabc0d</td>\n",
       "      <td>cfd035fc7e0a53b2</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>c2f2023d279665b2</td>\n",
       "      <td>...</td>\n",
       "      <td>100</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>ws7n</td>\n",
       "      <td>105.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>ff71a556d86c4b8f</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>522</th>\n",
       "      <td>13713</td>\n",
       "      <td>102</td>\n",
       "      <td>30</td>\n",
       "      <td>19:16:41</td>\n",
       "      <td>643</td>\n",
       "      <td>155c9e1c32bd0fa2</td>\n",
       "      <td>a849491e1b943336</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>6d55c54c8b1056fb</td>\n",
       "      <td>...</td>\n",
       "      <td>84236</td>\n",
       "      <td>9fefed0a981dcb7a</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>ws7n</td>\n",
       "      <td>105.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>75006599f5efde56</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>971</th>\n",
       "      <td>13713</td>\n",
       "      <td>140</td>\n",
       "      <td>12</td>\n",
       "      <td>16:27:55</td>\n",
       "      <td>643</td>\n",
       "      <td>c5fc631370cabc0d</td>\n",
       "      <td>633616813e0b46c1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>c2f2023d279665b2</td>\n",
       "      <td>...</td>\n",
       "      <td>100</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>ws7n</td>\n",
       "      <td>105.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>18ef0543caa458bb</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>992</th>\n",
       "      <td>13713</td>\n",
       "      <td>102</td>\n",
       "      <td>12</td>\n",
       "      <td>16:21:46</td>\n",
       "      <td>643</td>\n",
       "      <td>155c9e1c32bd0fa2</td>\n",
       "      <td>cf9756916d980bf1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>6d55c54c8b1056fb</td>\n",
       "      <td>...</td>\n",
       "      <td>125790</td>\n",
       "      <td>9fefed0a981dcb7a</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>ws7n</td>\n",
       "      <td>105.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>18ef0543caa458bb</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1024</th>\n",
       "      <td>13713</td>\n",
       "      <td>140</td>\n",
       "      <td>22</td>\n",
       "      <td>09:47:57</td>\n",
       "      <td>127</td>\n",
       "      <td>c5fc631370cabc0d</td>\n",
       "      <td>423d389c00ec2d6e</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>c2f2023d279665b2</td>\n",
       "      <td>...</td>\n",
       "      <td>100</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>ws7n</td>\n",
       "      <td>105.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>ff71a556d86c4b8f</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1657</th>\n",
       "      <td>13713</td>\n",
       "      <td>102</td>\n",
       "      <td>30</td>\n",
       "      <td>19:22:48</td>\n",
       "      <td>643</td>\n",
       "      <td>155c9e1c32bd0fa2</td>\n",
       "      <td>e723a571fa9b90f3</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>6d55c54c8b1056fb</td>\n",
       "      <td>...</td>\n",
       "      <td>76625</td>\n",
       "      <td>9fefed0a981dcb7a</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>ws7n</td>\n",
       "      <td>105.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>75006599f5efde56</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1680</th>\n",
       "      <td>13713</td>\n",
       "      <td>102</td>\n",
       "      <td>30</td>\n",
       "      <td>19:26:22</td>\n",
       "      <td>643</td>\n",
       "      <td>155c9e1c32bd0fa2</td>\n",
       "      <td>04d6a83f097dc9e5</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>6d55c54c8b1056fb</td>\n",
       "      <td>...</td>\n",
       "      <td>71188</td>\n",
       "      <td>9fefed0a981dcb7a</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>ws7n</td>\n",
       "      <td>105.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>75006599f5efde56</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1981</th>\n",
       "      <td>13713</td>\n",
       "      <td>140</td>\n",
       "      <td>12</td>\n",
       "      <td>17:42:55</td>\n",
       "      <td>643</td>\n",
       "      <td>4d7831c6f695ab19</td>\n",
       "      <td>caa4d44aabe1b7ba</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>c2f2023d279665b2</td>\n",
       "      <td>...</td>\n",
       "      <td>100</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>ws7n</td>\n",
       "      <td>105.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>18ef0543caa458bb</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2216</th>\n",
       "      <td>13713</td>\n",
       "      <td>140</td>\n",
       "      <td>30</td>\n",
       "      <td>19:36:33</td>\n",
       "      <td>643</td>\n",
       "      <td>4d7831c6f695ab19</td>\n",
       "      <td>1fde49816300c109</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>c2f2023d279665b2</td>\n",
       "      <td>...</td>\n",
       "      <td>100</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>ws7n</td>\n",
       "      <td>105.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>75006599f5efde56</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2243</th>\n",
       "      <td>13713</td>\n",
       "      <td>102</td>\n",
       "      <td>12</td>\n",
       "      <td>16:30:15</td>\n",
       "      <td>643</td>\n",
       "      <td>155c9e1c32bd0fa2</td>\n",
       "      <td>479765e651d2cac6</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>6d55c54c8b1056fb</td>\n",
       "      <td>...</td>\n",
       "      <td>118731</td>\n",
       "      <td>9fefed0a981dcb7a</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>ws7n</td>\n",
       "      <td>105.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>18ef0543caa458bb</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2598</th>\n",
       "      <td>13713</td>\n",
       "      <td>102</td>\n",
       "      <td>15</td>\n",
       "      <td>22:37:57</td>\n",
       "      <td>371</td>\n",
       "      <td>155c9e1c32bd0fa2</td>\n",
       "      <td>e36d1d861d5fc9ec</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>6d55c54c8b1056fb</td>\n",
       "      <td>...</td>\n",
       "      <td>52456</td>\n",
       "      <td>9a8ee16bde15e38a</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>ws7n</td>\n",
       "      <td>102.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>18ef0543caa458bb</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3060</th>\n",
       "      <td>13713</td>\n",
       "      <td>140</td>\n",
       "      <td>22</td>\n",
       "      <td>10:07:49</td>\n",
       "      <td>616</td>\n",
       "      <td>4d7831c6f695ab19</td>\n",
       "      <td>2ddf61e75fa65c4d</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>c2f2023d279665b2</td>\n",
       "      <td>...</td>\n",
       "      <td>100</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>ws7n</td>\n",
       "      <td>105.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>ff71a556d86c4b8f</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3774</th>\n",
       "      <td>13713</td>\n",
       "      <td>102</td>\n",
       "      <td>22</td>\n",
       "      <td>10:48:24</td>\n",
       "      <td>102</td>\n",
       "      <td>acdbdb842ac20f1e</td>\n",
       "      <td>d8babe2d19fa0c08</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>26bcf43a19df14c8</td>\n",
       "      <td>...</td>\n",
       "      <td>23681</td>\n",
       "      <td>9fefed0a981dcb7a</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>ws7n</td>\n",
       "      <td>102.0</td>\n",
       "      <td>64a3d9560ea1c954</td>\n",
       "      <td>1.0</td>\n",
       "      <td>ff71a556d86c4b8f</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4484</th>\n",
       "      <td>13713</td>\n",
       "      <td>140</td>\n",
       "      <td>22</td>\n",
       "      <td>09:52:45</td>\n",
       "      <td>616</td>\n",
       "      <td>4d7831c6f695ab19</td>\n",
       "      <td>374c262d6830daf2</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>c2f2023d279665b2</td>\n",
       "      <td>...</td>\n",
       "      <td>100</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>ws7n</td>\n",
       "      <td>105.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>ff71a556d86c4b8f</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4800</th>\n",
       "      <td>13713</td>\n",
       "      <td>140</td>\n",
       "      <td>5</td>\n",
       "      <td>17:41:42</td>\n",
       "      <td>2818</td>\n",
       "      <td>4d7831c6f695ab19</td>\n",
       "      <td>344b1fe284c5fb68</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>c2f2023d279665b2</td>\n",
       "      <td>...</td>\n",
       "      <td>100</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>ws7n</td>\n",
       "      <td>105.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>fbdeac5ab580fda7</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4818</th>\n",
       "      <td>13713</td>\n",
       "      <td>102</td>\n",
       "      <td>12</td>\n",
       "      <td>17:49:50</td>\n",
       "      <td>643</td>\n",
       "      <td>155c9e1c32bd0fa2</td>\n",
       "      <td>6ae24d2a84b21ca2</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>6d55c54c8b1056fb</td>\n",
       "      <td>...</td>\n",
       "      <td>69894</td>\n",
       "      <td>9fefed0a981dcb7a</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>ws7n</td>\n",
       "      <td>105.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>18ef0543caa458bb</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5085</th>\n",
       "      <td>13713</td>\n",
       "      <td>140</td>\n",
       "      <td>30</td>\n",
       "      <td>19:29:28</td>\n",
       "      <td>643</td>\n",
       "      <td>4d7831c6f695ab19</td>\n",
       "      <td>a566a03a89a76e4b</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>c2f2023d279665b2</td>\n",
       "      <td>...</td>\n",
       "      <td>100</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>ws7n</td>\n",
       "      <td>105.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>75006599f5efde56</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5479</th>\n",
       "      <td>13713</td>\n",
       "      <td>102</td>\n",
       "      <td>13</td>\n",
       "      <td>15:00:58</td>\n",
       "      <td>643</td>\n",
       "      <td>155c9e1c32bd0fa2</td>\n",
       "      <td>9ea35d6be00ff557</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>6d55c54c8b1056fb</td>\n",
       "      <td>...</td>\n",
       "      <td>35706</td>\n",
       "      <td>9fefed0a981dcb7a</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>ws7n</td>\n",
       "      <td>105.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>18ef0543caa458bb</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5626</th>\n",
       "      <td>13713</td>\n",
       "      <td>102</td>\n",
       "      <td>8</td>\n",
       "      <td>08:32:58</td>\n",
       "      <td>102</td>\n",
       "      <td>acdbdb842ac20f1e</td>\n",
       "      <td>d8babe2d19fa0c08</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>26bcf43a19df14c8</td>\n",
       "      <td>...</td>\n",
       "      <td>24648</td>\n",
       "      <td>9fefed0a981dcb7a</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>ws7n</td>\n",
       "      <td>102.0</td>\n",
       "      <td>3d8271370e1d6a93</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1896ab96542fc6c8</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6069</th>\n",
       "      <td>13713</td>\n",
       "      <td>140</td>\n",
       "      <td>22</td>\n",
       "      <td>09:31:58</td>\n",
       "      <td>643</td>\n",
       "      <td>c5fc631370cabc0d</td>\n",
       "      <td>983fcf50d6f6c0b2</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>c2f2023d279665b2</td>\n",
       "      <td>...</td>\n",
       "      <td>100</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>ws7n</td>\n",
       "      <td>105.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>ff71a556d86c4b8f</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7188</th>\n",
       "      <td>13713</td>\n",
       "      <td>102</td>\n",
       "      <td>12</td>\n",
       "      <td>17:44:54</td>\n",
       "      <td>643</td>\n",
       "      <td>155c9e1c32bd0fa2</td>\n",
       "      <td>f496be99e04b037d</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>6d55c54c8b1056fb</td>\n",
       "      <td>...</td>\n",
       "      <td>75325</td>\n",
       "      <td>9fefed0a981dcb7a</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>ws7n</td>\n",
       "      <td>105.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>18ef0543caa458bb</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7919</th>\n",
       "      <td>13713</td>\n",
       "      <td>102</td>\n",
       "      <td>17</td>\n",
       "      <td>14:55:05</td>\n",
       "      <td>9613</td>\n",
       "      <td>155c9e1c32bd0fa2</td>\n",
       "      <td>3bd5cf7c40962299</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>e0d7b8768da99dd4</td>\n",
       "      <td>...</td>\n",
       "      <td>100</td>\n",
       "      <td>cf6e3a074407c379</td>\n",
       "      <td>dba387d54bba26e4</td>\n",
       "      <td>5ffcde94e56ab237</td>\n",
       "      <td>ws7n</td>\n",
       "      <td>102.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>ff71a556d86c4b8f</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8005</th>\n",
       "      <td>13713</td>\n",
       "      <td>102</td>\n",
       "      <td>30</td>\n",
       "      <td>19:13:27</td>\n",
       "      <td>643</td>\n",
       "      <td>155c9e1c32bd0fa2</td>\n",
       "      <td>1e6a8ee3d6b51c5d</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>6d55c54c8b1056fb</td>\n",
       "      <td>...</td>\n",
       "      <td>88585</td>\n",
       "      <td>9fefed0a981dcb7a</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>ws7n</td>\n",
       "      <td>105.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>75006599f5efde56</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8189</th>\n",
       "      <td>13713</td>\n",
       "      <td>102</td>\n",
       "      <td>12</td>\n",
       "      <td>17:48:28</td>\n",
       "      <td>643</td>\n",
       "      <td>155c9e1c32bd0fa2</td>\n",
       "      <td>76fca4105b4b8e47</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>6d55c54c8b1056fb</td>\n",
       "      <td>...</td>\n",
       "      <td>72069</td>\n",
       "      <td>9fefed0a981dcb7a</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>ws7n</td>\n",
       "      <td>105.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>18ef0543caa458bb</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8594</th>\n",
       "      <td>13713</td>\n",
       "      <td>102</td>\n",
       "      <td>12</td>\n",
       "      <td>17:32:41</td>\n",
       "      <td>643</td>\n",
       "      <td>155c9e1c32bd0fa2</td>\n",
       "      <td>0ad13100b138aa0d</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>6d55c54c8b1056fb</td>\n",
       "      <td>...</td>\n",
       "      <td>93228</td>\n",
       "      <td>9fefed0a981dcb7a</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>ws7n</td>\n",
       "      <td>105.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>18ef0543caa458bb</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8628</th>\n",
       "      <td>13713</td>\n",
       "      <td>140</td>\n",
       "      <td>22</td>\n",
       "      <td>09:37:29</td>\n",
       "      <td>616</td>\n",
       "      <td>4d7831c6f695ab19</td>\n",
       "      <td>28e58d04e222d959</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>c2f2023d279665b2</td>\n",
       "      <td>...</td>\n",
       "      <td>100</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>ws7n</td>\n",
       "      <td>105.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>ff71a556d86c4b8f</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9020</th>\n",
       "      <td>13713</td>\n",
       "      <td>102</td>\n",
       "      <td>15</td>\n",
       "      <td>22:36:43</td>\n",
       "      <td>1459</td>\n",
       "      <td>155c9e1c32bd0fa2</td>\n",
       "      <td>d8babe2d19fa0c08</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>6d55c54c8b1056fb</td>\n",
       "      <td>...</td>\n",
       "      <td>55153</td>\n",
       "      <td>9fefed0a981dcb7a</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>ws7n</td>\n",
       "      <td>102.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>18ef0543caa458bb</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9966</th>\n",
       "      <td>13713</td>\n",
       "      <td>140</td>\n",
       "      <td>12</td>\n",
       "      <td>16:40:04</td>\n",
       "      <td>643</td>\n",
       "      <td>4d7831c6f695ab19</td>\n",
       "      <td>efccc5246fff3452</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>c2f2023d279665b2</td>\n",
       "      <td>...</td>\n",
       "      <td>100</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>ws7n</td>\n",
       "      <td>105.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>18ef0543caa458bb</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10048</th>\n",
       "      <td>13713</td>\n",
       "      <td>102</td>\n",
       "      <td>28</td>\n",
       "      <td>09:41:55</td>\n",
       "      <td>8254</td>\n",
       "      <td>155c9e1c32bd0fa2</td>\n",
       "      <td>3bd5cf7c40962299</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>e0d7b8768da99dd4</td>\n",
       "      <td>...</td>\n",
       "      <td>100</td>\n",
       "      <td>cf6e3a074407c379</td>\n",
       "      <td>dba387d54bba26e4</td>\n",
       "      <td>15b0bea817b4d014</td>\n",
       "      <td>ws7n</td>\n",
       "      <td>102.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>f65616378b61c84a</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10092</th>\n",
       "      <td>13713</td>\n",
       "      <td>140</td>\n",
       "      <td>30</td>\n",
       "      <td>19:27:51</td>\n",
       "      <td>643</td>\n",
       "      <td>4d7831c6f695ab19</td>\n",
       "      <td>1b5e213a8ddaa23b</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>c2f2023d279665b2</td>\n",
       "      <td>...</td>\n",
       "      <td>100</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>ws7n</td>\n",
       "      <td>105.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>75006599f5efde56</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>152969</th>\n",
       "      <td>13713</td>\n",
       "      <td>140</td>\n",
       "      <td>22</td>\n",
       "      <td>10:09:26</td>\n",
       "      <td>616</td>\n",
       "      <td>4d7831c6f695ab19</td>\n",
       "      <td>2d58b61e19585a41</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>c2f2023d279665b2</td>\n",
       "      <td>...</td>\n",
       "      <td>100</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>ws7n</td>\n",
       "      <td>105.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>ff71a556d86c4b8f</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>153422</th>\n",
       "      <td>13713</td>\n",
       "      <td>102</td>\n",
       "      <td>12</td>\n",
       "      <td>16:35:28</td>\n",
       "      <td>643</td>\n",
       "      <td>155c9e1c32bd0fa2</td>\n",
       "      <td>cd694f3397cfe18c</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>6d55c54c8b1056fb</td>\n",
       "      <td>...</td>\n",
       "      <td>111136</td>\n",
       "      <td>9fefed0a981dcb7a</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>ws7n</td>\n",
       "      <td>105.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>18ef0543caa458bb</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>153432</th>\n",
       "      <td>13713</td>\n",
       "      <td>102</td>\n",
       "      <td>30</td>\n",
       "      <td>19:19:22</td>\n",
       "      <td>643</td>\n",
       "      <td>155c9e1c32bd0fa2</td>\n",
       "      <td>1409ecde04941bb7</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>6d55c54c8b1056fb</td>\n",
       "      <td>...</td>\n",
       "      <td>80974</td>\n",
       "      <td>9fefed0a981dcb7a</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>ws7n</td>\n",
       "      <td>105.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>75006599f5efde56</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>153692</th>\n",
       "      <td>13713</td>\n",
       "      <td>102</td>\n",
       "      <td>2</td>\n",
       "      <td>11:39:57</td>\n",
       "      <td>8526</td>\n",
       "      <td>9451ef3c5a0d6807</td>\n",
       "      <td>3bd5cf7c40962299</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>e0d7b8768da99dd4</td>\n",
       "      <td>...</td>\n",
       "      <td>100</td>\n",
       "      <td>1ca672c7cb34af43</td>\n",
       "      <td>dfccbbab732ca90d</td>\n",
       "      <td>df0ad5d770285c01</td>\n",
       "      <td>ws7n</td>\n",
       "      <td>102.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>5f1ac7fe117cbaba</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>153878</th>\n",
       "      <td>13713</td>\n",
       "      <td>102</td>\n",
       "      <td>5</td>\n",
       "      <td>17:45:12</td>\n",
       "      <td>2818</td>\n",
       "      <td>155c9e1c32bd0fa2</td>\n",
       "      <td>9a4b079dd39448b1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>6d55c54c8b1056fb</td>\n",
       "      <td>...</td>\n",
       "      <td>43940</td>\n",
       "      <td>9fefed0a981dcb7a</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>ws7n</td>\n",
       "      <td>105.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>fbdeac5ab580fda7</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>154245</th>\n",
       "      <td>13713</td>\n",
       "      <td>102</td>\n",
       "      <td>13</td>\n",
       "      <td>15:01:43</td>\n",
       "      <td>643</td>\n",
       "      <td>155c9e1c32bd0fa2</td>\n",
       "      <td>9d10afccb08f5f62</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>6d55c54c8b1056fb</td>\n",
       "      <td>...</td>\n",
       "      <td>34624</td>\n",
       "      <td>9fefed0a981dcb7a</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>ws7n</td>\n",
       "      <td>105.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>18ef0543caa458bb</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>155357</th>\n",
       "      <td>13713</td>\n",
       "      <td>102</td>\n",
       "      <td>12</td>\n",
       "      <td>17:37:22</td>\n",
       "      <td>643</td>\n",
       "      <td>155c9e1c32bd0fa2</td>\n",
       "      <td>aeeffbd9ae529ec6</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>6d55c54c8b1056fb</td>\n",
       "      <td>...</td>\n",
       "      <td>85628</td>\n",
       "      <td>9fefed0a981dcb7a</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>ws7n</td>\n",
       "      <td>105.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>18ef0543caa458bb</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>155477</th>\n",
       "      <td>13713</td>\n",
       "      <td>102</td>\n",
       "      <td>12</td>\n",
       "      <td>17:48:47</td>\n",
       "      <td>643</td>\n",
       "      <td>155c9e1c32bd0fa2</td>\n",
       "      <td>a2fa7e82b33c41aa</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>6d55c54c8b1056fb</td>\n",
       "      <td>...</td>\n",
       "      <td>71525</td>\n",
       "      <td>9fefed0a981dcb7a</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>ws7n</td>\n",
       "      <td>105.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>18ef0543caa458bb</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>155734</th>\n",
       "      <td>13713</td>\n",
       "      <td>102</td>\n",
       "      <td>12</td>\n",
       "      <td>16:23:09</td>\n",
       "      <td>643</td>\n",
       "      <td>155c9e1c32bd0fa2</td>\n",
       "      <td>5363e976219cb9fa</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>6d55c54c8b1056fb</td>\n",
       "      <td>...</td>\n",
       "      <td>123616</td>\n",
       "      <td>9fefed0a981dcb7a</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>ws7n</td>\n",
       "      <td>105.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>18ef0543caa458bb</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>156130</th>\n",
       "      <td>13713</td>\n",
       "      <td>102</td>\n",
       "      <td>30</td>\n",
       "      <td>19:19:41</td>\n",
       "      <td>643</td>\n",
       "      <td>155c9e1c32bd0fa2</td>\n",
       "      <td>3812fb15f83e3024</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>6d55c54c8b1056fb</td>\n",
       "      <td>...</td>\n",
       "      <td>80430</td>\n",
       "      <td>9fefed0a981dcb7a</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>ws7n</td>\n",
       "      <td>105.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>75006599f5efde56</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>156771</th>\n",
       "      <td>13713</td>\n",
       "      <td>102</td>\n",
       "      <td>12</td>\n",
       "      <td>16:31:22</td>\n",
       "      <td>643</td>\n",
       "      <td>155c9e1c32bd0fa2</td>\n",
       "      <td>085f465cb810aab8</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>6d55c54c8b1056fb</td>\n",
       "      <td>...</td>\n",
       "      <td>117105</td>\n",
       "      <td>9fefed0a981dcb7a</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>ws7n</td>\n",
       "      <td>105.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>18ef0543caa458bb</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>157119</th>\n",
       "      <td>13713</td>\n",
       "      <td>102</td>\n",
       "      <td>22</td>\n",
       "      <td>10:48:24</td>\n",
       "      <td>27010</td>\n",
       "      <td>155c9e1c32bd0fa2</td>\n",
       "      <td>d8babe2d19fa0c08</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>6d55c54c8b1056fb</td>\n",
       "      <td>...</td>\n",
       "      <td>23681</td>\n",
       "      <td>9fefed0a981dcb7a</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>ws7n</td>\n",
       "      <td>102.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>ff71a556d86c4b8f</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>157179</th>\n",
       "      <td>13713</td>\n",
       "      <td>140</td>\n",
       "      <td>22</td>\n",
       "      <td>09:53:32</td>\n",
       "      <td>643</td>\n",
       "      <td>4d7831c6f695ab19</td>\n",
       "      <td>841e94fead5b3bd6</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>c2f2023d279665b2</td>\n",
       "      <td>...</td>\n",
       "      <td>100</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>ws7n</td>\n",
       "      <td>105.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>ff71a556d86c4b8f</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>157258</th>\n",
       "      <td>13713</td>\n",
       "      <td>102</td>\n",
       "      <td>12</td>\n",
       "      <td>17:41:41</td>\n",
       "      <td>643</td>\n",
       "      <td>155c9e1c32bd0fa2</td>\n",
       "      <td>48136bbb02234d4e</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>6d55c54c8b1056fb</td>\n",
       "      <td>...</td>\n",
       "      <td>80205</td>\n",
       "      <td>9fefed0a981dcb7a</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>ws7n</td>\n",
       "      <td>105.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>18ef0543caa458bb</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>157286</th>\n",
       "      <td>13713</td>\n",
       "      <td>102</td>\n",
       "      <td>8</td>\n",
       "      <td>09:41:03</td>\n",
       "      <td>102</td>\n",
       "      <td>acdbdb842ac20f1e</td>\n",
       "      <td>d8babe2d19fa0c08</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>26bcf43a19df14c8</td>\n",
       "      <td>...</td>\n",
       "      <td>3304</td>\n",
       "      <td>9fefed0a981dcb7a</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>ws7n</td>\n",
       "      <td>102.0</td>\n",
       "      <td>64a3d9560ea1c954</td>\n",
       "      <td>1.0</td>\n",
       "      <td>2052fc30ea425142</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>157357</th>\n",
       "      <td>13713</td>\n",
       "      <td>102</td>\n",
       "      <td>30</td>\n",
       "      <td>19:08:35</td>\n",
       "      <td>643</td>\n",
       "      <td>155c9e1c32bd0fa2</td>\n",
       "      <td>fef1f0505cd69c1a</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>6d55c54c8b1056fb</td>\n",
       "      <td>...</td>\n",
       "      <td>95653</td>\n",
       "      <td>9fefed0a981dcb7a</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>ws7n</td>\n",
       "      <td>105.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>75006599f5efde56</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>157462</th>\n",
       "      <td>13713</td>\n",
       "      <td>140</td>\n",
       "      <td>22</td>\n",
       "      <td>10:08:58</td>\n",
       "      <td>127</td>\n",
       "      <td>c5fc631370cabc0d</td>\n",
       "      <td>ee4652e40ce0a67f</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>c2f2023d279665b2</td>\n",
       "      <td>...</td>\n",
       "      <td>100</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>ws7n</td>\n",
       "      <td>105.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>ff71a556d86c4b8f</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>157942</th>\n",
       "      <td>13713</td>\n",
       "      <td>140</td>\n",
       "      <td>22</td>\n",
       "      <td>09:42:24</td>\n",
       "      <td>127</td>\n",
       "      <td>c5fc631370cabc0d</td>\n",
       "      <td>4c1443659ea6986c</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>c2f2023d279665b2</td>\n",
       "      <td>...</td>\n",
       "      <td>100</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>ws7n</td>\n",
       "      <td>105.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>ff71a556d86c4b8f</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>158578</th>\n",
       "      <td>13713</td>\n",
       "      <td>102</td>\n",
       "      <td>30</td>\n",
       "      <td>19:25:15</td>\n",
       "      <td>643</td>\n",
       "      <td>155c9e1c32bd0fa2</td>\n",
       "      <td>8ecf4325b9b9e7b6</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>6d55c54c8b1056fb</td>\n",
       "      <td>...</td>\n",
       "      <td>72819</td>\n",
       "      <td>9fefed0a981dcb7a</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>ws7n</td>\n",
       "      <td>105.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>75006599f5efde56</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>158771</th>\n",
       "      <td>13713</td>\n",
       "      <td>140</td>\n",
       "      <td>22</td>\n",
       "      <td>09:32:24</td>\n",
       "      <td>643</td>\n",
       "      <td>c5fc631370cabc0d</td>\n",
       "      <td>c7b962cbb9bb4e3f</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>c2f2023d279665b2</td>\n",
       "      <td>...</td>\n",
       "      <td>100</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>ws7n</td>\n",
       "      <td>105.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>ff71a556d86c4b8f</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>159104</th>\n",
       "      <td>13713</td>\n",
       "      <td>140</td>\n",
       "      <td>22</td>\n",
       "      <td>09:42:24</td>\n",
       "      <td>616</td>\n",
       "      <td>4d7831c6f695ab19</td>\n",
       "      <td>4c1443659ea6986c</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>c2f2023d279665b2</td>\n",
       "      <td>...</td>\n",
       "      <td>100</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>ws7n</td>\n",
       "      <td>105.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>ff71a556d86c4b8f</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>159133</th>\n",
       "      <td>13713</td>\n",
       "      <td>140</td>\n",
       "      <td>22</td>\n",
       "      <td>10:23:05</td>\n",
       "      <td>127</td>\n",
       "      <td>c5fc631370cabc0d</td>\n",
       "      <td>e63e42cab32acf87</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>c2f2023d279665b2</td>\n",
       "      <td>...</td>\n",
       "      <td>100</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>ws7n</td>\n",
       "      <td>105.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>ff71a556d86c4b8f</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>160043</th>\n",
       "      <td>13713</td>\n",
       "      <td>140</td>\n",
       "      <td>5</td>\n",
       "      <td>17:31:25</td>\n",
       "      <td>2818</td>\n",
       "      <td>4d7831c6f695ab19</td>\n",
       "      <td>29e31ee10b162cad</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>c2f2023d279665b2</td>\n",
       "      <td>...</td>\n",
       "      <td>100</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>ws7n</td>\n",
       "      <td>105.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>fbdeac5ab580fda7</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>160056</th>\n",
       "      <td>13713</td>\n",
       "      <td>102</td>\n",
       "      <td>8</td>\n",
       "      <td>09:40:35</td>\n",
       "      <td>371</td>\n",
       "      <td>155c9e1c32bd0fa2</td>\n",
       "      <td>d8babe2d19fa0c08</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>6d55c54c8b1056fb</td>\n",
       "      <td>...</td>\n",
       "      <td>3845</td>\n",
       "      <td>9a8ee16bde15e38a</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>ws7n</td>\n",
       "      <td>102.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2052fc30ea425142</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>160106</th>\n",
       "      <td>13713</td>\n",
       "      <td>102</td>\n",
       "      <td>30</td>\n",
       "      <td>19:31:41</td>\n",
       "      <td>643</td>\n",
       "      <td>155c9e1c32bd0fa2</td>\n",
       "      <td>01c70eebad88c6cf</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>6d55c54c8b1056fb</td>\n",
       "      <td>...</td>\n",
       "      <td>65751</td>\n",
       "      <td>9fefed0a981dcb7a</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>ws7n</td>\n",
       "      <td>105.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>75006599f5efde56</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>160150</th>\n",
       "      <td>13713</td>\n",
       "      <td>102</td>\n",
       "      <td>30</td>\n",
       "      <td>19:34:42</td>\n",
       "      <td>643</td>\n",
       "      <td>155c9e1c32bd0fa2</td>\n",
       "      <td>753355903eb10be5</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>6d55c54c8b1056fb</td>\n",
       "      <td>...</td>\n",
       "      <td>61402</td>\n",
       "      <td>9fefed0a981dcb7a</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>ws7n</td>\n",
       "      <td>105.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>75006599f5efde56</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>160820</th>\n",
       "      <td>13713</td>\n",
       "      <td>140</td>\n",
       "      <td>22</td>\n",
       "      <td>10:15:52</td>\n",
       "      <td>616</td>\n",
       "      <td>4d7831c6f695ab19</td>\n",
       "      <td>79b0bb4ed02430b2</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>c2f2023d279665b2</td>\n",
       "      <td>...</td>\n",
       "      <td>100</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>ws7n</td>\n",
       "      <td>105.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>ff71a556d86c4b8f</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>161049</th>\n",
       "      <td>13713</td>\n",
       "      <td>140</td>\n",
       "      <td>22</td>\n",
       "      <td>09:39:01</td>\n",
       "      <td>127</td>\n",
       "      <td>c5fc631370cabc0d</td>\n",
       "      <td>0bffb2d2710dd742</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>c2f2023d279665b2</td>\n",
       "      <td>...</td>\n",
       "      <td>100</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>ws7n</td>\n",
       "      <td>105.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>ff71a556d86c4b8f</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>161384</th>\n",
       "      <td>13713</td>\n",
       "      <td>140</td>\n",
       "      <td>22</td>\n",
       "      <td>10:22:41</td>\n",
       "      <td>643</td>\n",
       "      <td>4d7831c6f695ab19</td>\n",
       "      <td>ad2a5276b0c99a82</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>c2f2023d279665b2</td>\n",
       "      <td>...</td>\n",
       "      <td>100</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>ws7n</td>\n",
       "      <td>105.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>ff71a556d86c4b8f</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>161716</th>\n",
       "      <td>13713</td>\n",
       "      <td>102</td>\n",
       "      <td>12</td>\n",
       "      <td>17:38:18</td>\n",
       "      <td>643</td>\n",
       "      <td>155c9e1c32bd0fa2</td>\n",
       "      <td>bbcdae06a9c3ab45</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>6d55c54c8b1056fb</td>\n",
       "      <td>...</td>\n",
       "      <td>84007</td>\n",
       "      <td>9fefed0a981dcb7a</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>ws7n</td>\n",
       "      <td>105.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>18ef0543caa458bb</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>485 rows × 28 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "          UID  channel  day      time  trans_amt          amt_src1  \\\n",
       "2       13713      140   22  09:56:39        127  c5fc631370cabc0d   \n",
       "522     13713      102   30  19:16:41        643  155c9e1c32bd0fa2   \n",
       "971     13713      140   12  16:27:55        643  c5fc631370cabc0d   \n",
       "992     13713      102   12  16:21:46        643  155c9e1c32bd0fa2   \n",
       "1024    13713      140   22  09:47:57        127  c5fc631370cabc0d   \n",
       "1657    13713      102   30  19:22:48        643  155c9e1c32bd0fa2   \n",
       "1680    13713      102   30  19:26:22        643  155c9e1c32bd0fa2   \n",
       "1981    13713      140   12  17:42:55        643  4d7831c6f695ab19   \n",
       "2216    13713      140   30  19:36:33        643  4d7831c6f695ab19   \n",
       "2243    13713      102   12  16:30:15        643  155c9e1c32bd0fa2   \n",
       "2598    13713      102   15  22:37:57        371  155c9e1c32bd0fa2   \n",
       "3060    13713      140   22  10:07:49        616  4d7831c6f695ab19   \n",
       "3774    13713      102   22  10:48:24        102  acdbdb842ac20f1e   \n",
       "4484    13713      140   22  09:52:45        616  4d7831c6f695ab19   \n",
       "4800    13713      140    5  17:41:42       2818  4d7831c6f695ab19   \n",
       "4818    13713      102   12  17:49:50        643  155c9e1c32bd0fa2   \n",
       "5085    13713      140   30  19:29:28        643  4d7831c6f695ab19   \n",
       "5479    13713      102   13  15:00:58        643  155c9e1c32bd0fa2   \n",
       "5626    13713      102    8  08:32:58        102  acdbdb842ac20f1e   \n",
       "6069    13713      140   22  09:31:58        643  c5fc631370cabc0d   \n",
       "7188    13713      102   12  17:44:54        643  155c9e1c32bd0fa2   \n",
       "7919    13713      102   17  14:55:05       9613  155c9e1c32bd0fa2   \n",
       "8005    13713      102   30  19:13:27        643  155c9e1c32bd0fa2   \n",
       "8189    13713      102   12  17:48:28        643  155c9e1c32bd0fa2   \n",
       "8594    13713      102   12  17:32:41        643  155c9e1c32bd0fa2   \n",
       "8628    13713      140   22  09:37:29        616  4d7831c6f695ab19   \n",
       "9020    13713      102   15  22:36:43       1459  155c9e1c32bd0fa2   \n",
       "9966    13713      140   12  16:40:04        643  4d7831c6f695ab19   \n",
       "10048   13713      102   28  09:41:55       8254  155c9e1c32bd0fa2   \n",
       "10092   13713      140   30  19:27:51        643  4d7831c6f695ab19   \n",
       "...       ...      ...  ...       ...        ...               ...   \n",
       "152969  13713      140   22  10:09:26        616  4d7831c6f695ab19   \n",
       "153422  13713      102   12  16:35:28        643  155c9e1c32bd0fa2   \n",
       "153432  13713      102   30  19:19:22        643  155c9e1c32bd0fa2   \n",
       "153692  13713      102    2  11:39:57       8526  9451ef3c5a0d6807   \n",
       "153878  13713      102    5  17:45:12       2818  155c9e1c32bd0fa2   \n",
       "154245  13713      102   13  15:01:43        643  155c9e1c32bd0fa2   \n",
       "155357  13713      102   12  17:37:22        643  155c9e1c32bd0fa2   \n",
       "155477  13713      102   12  17:48:47        643  155c9e1c32bd0fa2   \n",
       "155734  13713      102   12  16:23:09        643  155c9e1c32bd0fa2   \n",
       "156130  13713      102   30  19:19:41        643  155c9e1c32bd0fa2   \n",
       "156771  13713      102   12  16:31:22        643  155c9e1c32bd0fa2   \n",
       "157119  13713      102   22  10:48:24      27010  155c9e1c32bd0fa2   \n",
       "157179  13713      140   22  09:53:32        643  4d7831c6f695ab19   \n",
       "157258  13713      102   12  17:41:41        643  155c9e1c32bd0fa2   \n",
       "157286  13713      102    8  09:41:03        102  acdbdb842ac20f1e   \n",
       "157357  13713      102   30  19:08:35        643  155c9e1c32bd0fa2   \n",
       "157462  13713      140   22  10:08:58        127  c5fc631370cabc0d   \n",
       "157942  13713      140   22  09:42:24        127  c5fc631370cabc0d   \n",
       "158578  13713      102   30  19:25:15        643  155c9e1c32bd0fa2   \n",
       "158771  13713      140   22  09:32:24        643  c5fc631370cabc0d   \n",
       "159104  13713      140   22  09:42:24        616  4d7831c6f695ab19   \n",
       "159133  13713      140   22  10:23:05        127  c5fc631370cabc0d   \n",
       "160043  13713      140    5  17:31:25       2818  4d7831c6f695ab19   \n",
       "160056  13713      102    8  09:40:35        371  155c9e1c32bd0fa2   \n",
       "160106  13713      102   30  19:31:41        643  155c9e1c32bd0fa2   \n",
       "160150  13713      102   30  19:34:42        643  155c9e1c32bd0fa2   \n",
       "160820  13713      140   22  10:15:52        616  4d7831c6f695ab19   \n",
       "161049  13713      140   22  09:39:01        127  c5fc631370cabc0d   \n",
       "161384  13713      140   22  10:22:41        643  4d7831c6f695ab19   \n",
       "161716  13713      102   12  17:38:18        643  155c9e1c32bd0fa2   \n",
       "\n",
       "                merchant code1 code2       trans_type1 ...     bal  \\\n",
       "2       cfd035fc7e0a53b2   NaN   NaN  c2f2023d279665b2 ...     100   \n",
       "522     a849491e1b943336   NaN   NaN  6d55c54c8b1056fb ...   84236   \n",
       "971     633616813e0b46c1   NaN   NaN  c2f2023d279665b2 ...     100   \n",
       "992     cf9756916d980bf1   NaN   NaN  6d55c54c8b1056fb ...  125790   \n",
       "1024    423d389c00ec2d6e   NaN   NaN  c2f2023d279665b2 ...     100   \n",
       "1657    e723a571fa9b90f3   NaN   NaN  6d55c54c8b1056fb ...   76625   \n",
       "1680    04d6a83f097dc9e5   NaN   NaN  6d55c54c8b1056fb ...   71188   \n",
       "1981    caa4d44aabe1b7ba   NaN   NaN  c2f2023d279665b2 ...     100   \n",
       "2216    1fde49816300c109   NaN   NaN  c2f2023d279665b2 ...     100   \n",
       "2243    479765e651d2cac6   NaN   NaN  6d55c54c8b1056fb ...  118731   \n",
       "2598    e36d1d861d5fc9ec   NaN   NaN  6d55c54c8b1056fb ...   52456   \n",
       "3060    2ddf61e75fa65c4d   NaN   NaN  c2f2023d279665b2 ...     100   \n",
       "3774    d8babe2d19fa0c08   NaN   NaN  26bcf43a19df14c8 ...   23681   \n",
       "4484    374c262d6830daf2   NaN   NaN  c2f2023d279665b2 ...     100   \n",
       "4800    344b1fe284c5fb68   NaN   NaN  c2f2023d279665b2 ...     100   \n",
       "4818    6ae24d2a84b21ca2   NaN   NaN  6d55c54c8b1056fb ...   69894   \n",
       "5085    a566a03a89a76e4b   NaN   NaN  c2f2023d279665b2 ...     100   \n",
       "5479    9ea35d6be00ff557   NaN   NaN  6d55c54c8b1056fb ...   35706   \n",
       "5626    d8babe2d19fa0c08   NaN   NaN  26bcf43a19df14c8 ...   24648   \n",
       "6069    983fcf50d6f6c0b2   NaN   NaN  c2f2023d279665b2 ...     100   \n",
       "7188    f496be99e04b037d   NaN   NaN  6d55c54c8b1056fb ...   75325   \n",
       "7919    3bd5cf7c40962299   NaN   NaN  e0d7b8768da99dd4 ...     100   \n",
       "8005    1e6a8ee3d6b51c5d   NaN   NaN  6d55c54c8b1056fb ...   88585   \n",
       "8189    76fca4105b4b8e47   NaN   NaN  6d55c54c8b1056fb ...   72069   \n",
       "8594    0ad13100b138aa0d   NaN   NaN  6d55c54c8b1056fb ...   93228   \n",
       "8628    28e58d04e222d959   NaN   NaN  c2f2023d279665b2 ...     100   \n",
       "9020    d8babe2d19fa0c08   NaN   NaN  6d55c54c8b1056fb ...   55153   \n",
       "9966    efccc5246fff3452   NaN   NaN  c2f2023d279665b2 ...     100   \n",
       "10048   3bd5cf7c40962299   NaN   NaN  e0d7b8768da99dd4 ...     100   \n",
       "10092   1b5e213a8ddaa23b   NaN   NaN  c2f2023d279665b2 ...     100   \n",
       "...                  ...   ...   ...               ... ...     ...   \n",
       "152969  2d58b61e19585a41   NaN   NaN  c2f2023d279665b2 ...     100   \n",
       "153422  cd694f3397cfe18c   NaN   NaN  6d55c54c8b1056fb ...  111136   \n",
       "153432  1409ecde04941bb7   NaN   NaN  6d55c54c8b1056fb ...   80974   \n",
       "153692  3bd5cf7c40962299   NaN   NaN  e0d7b8768da99dd4 ...     100   \n",
       "153878  9a4b079dd39448b1   NaN   NaN  6d55c54c8b1056fb ...   43940   \n",
       "154245  9d10afccb08f5f62   NaN   NaN  6d55c54c8b1056fb ...   34624   \n",
       "155357  aeeffbd9ae529ec6   NaN   NaN  6d55c54c8b1056fb ...   85628   \n",
       "155477  a2fa7e82b33c41aa   NaN   NaN  6d55c54c8b1056fb ...   71525   \n",
       "155734  5363e976219cb9fa   NaN   NaN  6d55c54c8b1056fb ...  123616   \n",
       "156130  3812fb15f83e3024   NaN   NaN  6d55c54c8b1056fb ...   80430   \n",
       "156771  085f465cb810aab8   NaN   NaN  6d55c54c8b1056fb ...  117105   \n",
       "157119  d8babe2d19fa0c08   NaN   NaN  6d55c54c8b1056fb ...   23681   \n",
       "157179  841e94fead5b3bd6   NaN   NaN  c2f2023d279665b2 ...     100   \n",
       "157258  48136bbb02234d4e   NaN   NaN  6d55c54c8b1056fb ...   80205   \n",
       "157286  d8babe2d19fa0c08   NaN   NaN  26bcf43a19df14c8 ...    3304   \n",
       "157357  fef1f0505cd69c1a   NaN   NaN  6d55c54c8b1056fb ...   95653   \n",
       "157462  ee4652e40ce0a67f   NaN   NaN  c2f2023d279665b2 ...     100   \n",
       "157942  4c1443659ea6986c   NaN   NaN  c2f2023d279665b2 ...     100   \n",
       "158578  8ecf4325b9b9e7b6   NaN   NaN  6d55c54c8b1056fb ...   72819   \n",
       "158771  c7b962cbb9bb4e3f   NaN   NaN  c2f2023d279665b2 ...     100   \n",
       "159104  4c1443659ea6986c   NaN   NaN  c2f2023d279665b2 ...     100   \n",
       "159133  e63e42cab32acf87   NaN   NaN  c2f2023d279665b2 ...     100   \n",
       "160043  29e31ee10b162cad   NaN   NaN  c2f2023d279665b2 ...     100   \n",
       "160056  d8babe2d19fa0c08   NaN   NaN  6d55c54c8b1056fb ...    3845   \n",
       "160106  01c70eebad88c6cf   NaN   NaN  6d55c54c8b1056fb ...   65751   \n",
       "160150  753355903eb10be5   NaN   NaN  6d55c54c8b1056fb ...   61402   \n",
       "160820  79b0bb4ed02430b2   NaN   NaN  c2f2023d279665b2 ...     100   \n",
       "161049  0bffb2d2710dd742   NaN   NaN  c2f2023d279665b2 ...     100   \n",
       "161384  ad2a5276b0c99a82   NaN   NaN  c2f2023d279665b2 ...     100   \n",
       "161716  bbcdae06a9c3ab45   NaN   NaN  6d55c54c8b1056fb ...   84007   \n",
       "\n",
       "                amt_src2           acc_id2           acc_id3 geo_code  \\\n",
       "2                    NaN               NaN               NaN     ws7n   \n",
       "522     9fefed0a981dcb7a               NaN               NaN     ws7n   \n",
       "971                  NaN               NaN               NaN     ws7n   \n",
       "992     9fefed0a981dcb7a               NaN               NaN     ws7n   \n",
       "1024                 NaN               NaN               NaN     ws7n   \n",
       "1657    9fefed0a981dcb7a               NaN               NaN     ws7n   \n",
       "1680    9fefed0a981dcb7a               NaN               NaN     ws7n   \n",
       "1981                 NaN               NaN               NaN     ws7n   \n",
       "2216                 NaN               NaN               NaN     ws7n   \n",
       "2243    9fefed0a981dcb7a               NaN               NaN     ws7n   \n",
       "2598    9a8ee16bde15e38a               NaN               NaN     ws7n   \n",
       "3060                 NaN               NaN               NaN     ws7n   \n",
       "3774    9fefed0a981dcb7a               NaN               NaN     ws7n   \n",
       "4484                 NaN               NaN               NaN     ws7n   \n",
       "4800                 NaN               NaN               NaN     ws7n   \n",
       "4818    9fefed0a981dcb7a               NaN               NaN     ws7n   \n",
       "5085                 NaN               NaN               NaN     ws7n   \n",
       "5479    9fefed0a981dcb7a               NaN               NaN     ws7n   \n",
       "5626    9fefed0a981dcb7a               NaN               NaN     ws7n   \n",
       "6069                 NaN               NaN               NaN     ws7n   \n",
       "7188    9fefed0a981dcb7a               NaN               NaN     ws7n   \n",
       "7919    cf6e3a074407c379  dba387d54bba26e4  5ffcde94e56ab237     ws7n   \n",
       "8005    9fefed0a981dcb7a               NaN               NaN     ws7n   \n",
       "8189    9fefed0a981dcb7a               NaN               NaN     ws7n   \n",
       "8594    9fefed0a981dcb7a               NaN               NaN     ws7n   \n",
       "8628                 NaN               NaN               NaN     ws7n   \n",
       "9020    9fefed0a981dcb7a               NaN               NaN     ws7n   \n",
       "9966                 NaN               NaN               NaN     ws7n   \n",
       "10048   cf6e3a074407c379  dba387d54bba26e4  15b0bea817b4d014     ws7n   \n",
       "10092                NaN               NaN               NaN     ws7n   \n",
       "...                  ...               ...               ...      ...   \n",
       "152969               NaN               NaN               NaN     ws7n   \n",
       "153422  9fefed0a981dcb7a               NaN               NaN     ws7n   \n",
       "153432  9fefed0a981dcb7a               NaN               NaN     ws7n   \n",
       "153692  1ca672c7cb34af43  dfccbbab732ca90d  df0ad5d770285c01     ws7n   \n",
       "153878  9fefed0a981dcb7a               NaN               NaN     ws7n   \n",
       "154245  9fefed0a981dcb7a               NaN               NaN     ws7n   \n",
       "155357  9fefed0a981dcb7a               NaN               NaN     ws7n   \n",
       "155477  9fefed0a981dcb7a               NaN               NaN     ws7n   \n",
       "155734  9fefed0a981dcb7a               NaN               NaN     ws7n   \n",
       "156130  9fefed0a981dcb7a               NaN               NaN     ws7n   \n",
       "156771  9fefed0a981dcb7a               NaN               NaN     ws7n   \n",
       "157119  9fefed0a981dcb7a               NaN               NaN     ws7n   \n",
       "157179               NaN               NaN               NaN     ws7n   \n",
       "157258  9fefed0a981dcb7a               NaN               NaN     ws7n   \n",
       "157286  9fefed0a981dcb7a               NaN               NaN     ws7n   \n",
       "157357  9fefed0a981dcb7a               NaN               NaN     ws7n   \n",
       "157462               NaN               NaN               NaN     ws7n   \n",
       "157942               NaN               NaN               NaN     ws7n   \n",
       "158578  9fefed0a981dcb7a               NaN               NaN     ws7n   \n",
       "158771               NaN               NaN               NaN     ws7n   \n",
       "159104               NaN               NaN               NaN     ws7n   \n",
       "159133               NaN               NaN               NaN     ws7n   \n",
       "160043               NaN               NaN               NaN     ws7n   \n",
       "160056  9a8ee16bde15e38a               NaN               NaN     ws7n   \n",
       "160106  9fefed0a981dcb7a               NaN               NaN     ws7n   \n",
       "160150  9fefed0a981dcb7a               NaN               NaN     ws7n   \n",
       "160820               NaN               NaN               NaN     ws7n   \n",
       "161049               NaN               NaN               NaN     ws7n   \n",
       "161384               NaN               NaN               NaN     ws7n   \n",
       "161716  9fefed0a981dcb7a               NaN               NaN     ws7n   \n",
       "\n",
       "       trans_type2       market_code market_type           ip1_sub Tag  \n",
       "2            105.0               NaN         NaN  ff71a556d86c4b8f   0  \n",
       "522          105.0               NaN         NaN  75006599f5efde56   0  \n",
       "971          105.0               NaN         NaN  18ef0543caa458bb   0  \n",
       "992          105.0               NaN         NaN  18ef0543caa458bb   0  \n",
       "1024         105.0               NaN         NaN  ff71a556d86c4b8f   0  \n",
       "1657         105.0               NaN         NaN  75006599f5efde56   0  \n",
       "1680         105.0               NaN         NaN  75006599f5efde56   0  \n",
       "1981         105.0               NaN         NaN  18ef0543caa458bb   0  \n",
       "2216         105.0               NaN         NaN  75006599f5efde56   0  \n",
       "2243         105.0               NaN         NaN  18ef0543caa458bb   0  \n",
       "2598         102.0               NaN         NaN  18ef0543caa458bb   0  \n",
       "3060         105.0               NaN         NaN  ff71a556d86c4b8f   0  \n",
       "3774         102.0  64a3d9560ea1c954         1.0  ff71a556d86c4b8f   0  \n",
       "4484         105.0               NaN         NaN  ff71a556d86c4b8f   0  \n",
       "4800         105.0               NaN         NaN  fbdeac5ab580fda7   0  \n",
       "4818         105.0               NaN         NaN  18ef0543caa458bb   0  \n",
       "5085         105.0               NaN         NaN  75006599f5efde56   0  \n",
       "5479         105.0               NaN         NaN  18ef0543caa458bb   0  \n",
       "5626         102.0  3d8271370e1d6a93         1.0  1896ab96542fc6c8   0  \n",
       "6069         105.0               NaN         NaN  ff71a556d86c4b8f   0  \n",
       "7188         105.0               NaN         NaN  18ef0543caa458bb   0  \n",
       "7919         102.0               NaN         NaN  ff71a556d86c4b8f   0  \n",
       "8005         105.0               NaN         NaN  75006599f5efde56   0  \n",
       "8189         105.0               NaN         NaN  18ef0543caa458bb   0  \n",
       "8594         105.0               NaN         NaN  18ef0543caa458bb   0  \n",
       "8628         105.0               NaN         NaN  ff71a556d86c4b8f   0  \n",
       "9020         102.0               NaN         NaN  18ef0543caa458bb   0  \n",
       "9966         105.0               NaN         NaN  18ef0543caa458bb   0  \n",
       "10048        102.0               NaN         NaN  f65616378b61c84a   0  \n",
       "10092        105.0               NaN         NaN  75006599f5efde56   0  \n",
       "...            ...               ...         ...               ...  ..  \n",
       "152969       105.0               NaN         NaN  ff71a556d86c4b8f   0  \n",
       "153422       105.0               NaN         NaN  18ef0543caa458bb   0  \n",
       "153432       105.0               NaN         NaN  75006599f5efde56   0  \n",
       "153692       102.0               NaN         NaN  5f1ac7fe117cbaba   0  \n",
       "153878       105.0               NaN         NaN  fbdeac5ab580fda7   0  \n",
       "154245       105.0               NaN         NaN  18ef0543caa458bb   0  \n",
       "155357       105.0               NaN         NaN  18ef0543caa458bb   0  \n",
       "155477       105.0               NaN         NaN  18ef0543caa458bb   0  \n",
       "155734       105.0               NaN         NaN  18ef0543caa458bb   0  \n",
       "156130       105.0               NaN         NaN  75006599f5efde56   0  \n",
       "156771       105.0               NaN         NaN  18ef0543caa458bb   0  \n",
       "157119       102.0               NaN         NaN  ff71a556d86c4b8f   0  \n",
       "157179       105.0               NaN         NaN  ff71a556d86c4b8f   0  \n",
       "157258       105.0               NaN         NaN  18ef0543caa458bb   0  \n",
       "157286       102.0  64a3d9560ea1c954         1.0  2052fc30ea425142   0  \n",
       "157357       105.0               NaN         NaN  75006599f5efde56   0  \n",
       "157462       105.0               NaN         NaN  ff71a556d86c4b8f   0  \n",
       "157942       105.0               NaN         NaN  ff71a556d86c4b8f   0  \n",
       "158578       105.0               NaN         NaN  75006599f5efde56   0  \n",
       "158771       105.0               NaN         NaN  ff71a556d86c4b8f   0  \n",
       "159104       105.0               NaN         NaN  ff71a556d86c4b8f   0  \n",
       "159133       105.0               NaN         NaN  ff71a556d86c4b8f   0  \n",
       "160043       105.0               NaN         NaN  fbdeac5ab580fda7   0  \n",
       "160056       102.0               NaN         NaN  2052fc30ea425142   0  \n",
       "160106       105.0               NaN         NaN  75006599f5efde56   0  \n",
       "160150       105.0               NaN         NaN  75006599f5efde56   0  \n",
       "160820       105.0               NaN         NaN  ff71a556d86c4b8f   0  \n",
       "161049       105.0               NaN         NaN  ff71a556d86c4b8f   0  \n",
       "161384       105.0               NaN         NaN  ff71a556d86c4b8f   0  \n",
       "161716       105.0               NaN         NaN  18ef0543caa458bb   0  \n",
       "\n",
       "[485 rows x 28 columns]"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "train_tsts[train_tsts.UID == 13713]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [],
   "source": [
    "train_tst_day = train_tsts.copy()\n",
    "\n",
    "'''每个用户的总共交易次数，以及每个用户平均每天的交易次数'''\n",
    "train_tst_feature = pd.merge(train_tst_feature, \n",
    "                        train_tst_day.groupby('UID', as_index=False)['day'].agg({'trans_count':'count'}))\n",
    "train_tst_day.drop_duplicates(['UID', 'day'], keep='first', inplace=True)\n",
    "train_tst_day = train_tst_day.groupby('UID', as_index=False)['day'].agg({'day_count':'count'})\n",
    "train_tst_feature = pd.merge(train_tst_feature, train_tst_day, on='UID', how='left')\n",
    "\n",
    "train_tst_feature['avg_day_trans'] = train_tst_feature.trans_count / train_tst_feature.day_count\n",
    "# train_tst_feature.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [],
   "source": [
    "'''统计每个不同时间段的交易次数，以及每个用户每个时间段的平均交易次数'''\n",
    "train_time = train_tsts.copy()\n",
    "train_time.time = train_time.time.str[:2]\n",
    "\n",
    "train_time.time = train_time.time.astype('int')\n",
    "\n",
    "def time_help(data):\n",
    "    if (data > 7) & (data <= 12):\n",
    "        return 0\n",
    "    elif (data > 12) & (data <= 19):\n",
    "        return 1\n",
    "    elif (data > 19) & (data <= 24):\n",
    "        return 2\n",
    "    elif (data >= 0) & (data <= 7):\n",
    "        return 3"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [],
   "source": [
    "train_time.time = train_time.time.map(time_help)\n",
    "train_time = pd.concat([train_time.UID, pd.get_dummies(train_time.time, prefix='time')], axis=1)\n",
    "train_time = train_time.groupby('UID', as_index=False).sum()\n",
    "train_tst_feature = pd.merge(train_tst_feature, train_time, on='UID', how='left')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [],
   "source": [
    "time_func = lambda x: 0 if x == np.inf else x\n",
    "train_tst_feature['avg_time0_trans'] = (train_tst_feature.trans_count / train_tst_feature.time_0).map(time_func)\n",
    "train_tst_feature['avg_time1_trans'] = (train_tst_feature.trans_count / train_tst_feature.time_1).map(time_func)\n",
    "train_tst_feature['avg_time2_trans'] = (train_tst_feature.trans_count / train_tst_feature.time_2).map(time_func)\n",
    "train_tst_feature['avg_time3_trans'] = (train_tst_feature.trans_count / train_tst_feature.time_3).map(time_func)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>UID</th>\n",
       "      <th>Tag</th>\n",
       "      <th>channel_119</th>\n",
       "      <th>trans_amt_than_119</th>\n",
       "      <th>trans_amt_102</th>\n",
       "      <th>trans_bal_big</th>\n",
       "      <th>bal_100</th>\n",
       "      <th>bal_100_260</th>\n",
       "      <th>day_is_1</th>\n",
       "      <th>day_is_8</th>\n",
       "      <th>...</th>\n",
       "      <th>day_count</th>\n",
       "      <th>avg_day_trans</th>\n",
       "      <th>time_0</th>\n",
       "      <th>time_1</th>\n",
       "      <th>time_2</th>\n",
       "      <th>time_3</th>\n",
       "      <th>avg_time0_trans</th>\n",
       "      <th>avg_time1_trans</th>\n",
       "      <th>avg_time2_trans</th>\n",
       "      <th>avg_time3_trans</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>19092</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0.105263</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>9</td>\n",
       "      <td>2.111111</td>\n",
       "      <td>5.0</td>\n",
       "      <td>9.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>3.800000</td>\n",
       "      <td>2.111111</td>\n",
       "      <td>9.5</td>\n",
       "      <td>6.333333</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>13465</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0.105263</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>8</td>\n",
       "      <td>2.375000</td>\n",
       "      <td>7.0</td>\n",
       "      <td>8.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>2.714286</td>\n",
       "      <td>2.375000</td>\n",
       "      <td>0.0</td>\n",
       "      <td>4.750000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>13713</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0.507216</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>...</td>\n",
       "      <td>17</td>\n",
       "      <td>28.529412</td>\n",
       "      <td>172.0</td>\n",
       "      <td>303.0</td>\n",
       "      <td>10.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>2.819767</td>\n",
       "      <td>1.600660</td>\n",
       "      <td>48.5</td>\n",
       "      <td>0.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>22703</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>2</td>\n",
       "      <td>3.000000</td>\n",
       "      <td>0.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>3.000000</td>\n",
       "      <td>3.0</td>\n",
       "      <td>3.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>17816</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0.285714</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>...</td>\n",
       "      <td>9</td>\n",
       "      <td>1.555556</td>\n",
       "      <td>4.0</td>\n",
       "      <td>10.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>3.500000</td>\n",
       "      <td>1.400000</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.000000</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 26 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "     UID  Tag  channel_119  trans_amt_than_119  trans_amt_102  trans_bal_big  \\\n",
       "0  19092    0            0                   0              0              0   \n",
       "1  13465    0            0                   0              0              0   \n",
       "2  13713    0            0                   0              1              0   \n",
       "3  22703    0            0                   0              0              0   \n",
       "4  17816    0            0                   0              0              0   \n",
       "\n",
       "   bal_100  bal_100_260  day_is_1  day_is_8       ...         day_count  \\\n",
       "0        1     0.105263         0         0       ...                 9   \n",
       "1        1     0.105263         0         0       ...                 8   \n",
       "2        1     0.507216         0         1       ...                17   \n",
       "3        1     0.000000         1         0       ...                 2   \n",
       "4        1     0.285714         1         1       ...                 9   \n",
       "\n",
       "   avg_day_trans  time_0  time_1  time_2  time_3  avg_time0_trans  \\\n",
       "0       2.111111     5.0     9.0     2.0     3.0         3.800000   \n",
       "1       2.375000     7.0     8.0     0.0     4.0         2.714286   \n",
       "2      28.529412   172.0   303.0    10.0     0.0         2.819767   \n",
       "3       3.000000     0.0     2.0     2.0     2.0         0.000000   \n",
       "4       1.555556     4.0    10.0     0.0     0.0         3.500000   \n",
       "\n",
       "   avg_time1_trans  avg_time2_trans  avg_time3_trans  \n",
       "0         2.111111              9.5         6.333333  \n",
       "1         2.375000              0.0         4.750000  \n",
       "2         1.600660             48.5         0.000000  \n",
       "3         3.000000              3.0         3.000000  \n",
       "4         1.400000              0.0         0.000000  \n",
       "\n",
       "[5 rows x 26 columns]"
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "train_tst_feature.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# 取每个用户的相邻两次交易天数差值的平均值，最大值，最大值"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [],
   "source": [
    "train_time_sub = train_tsts.copy()\n",
    "train_time_sub.drop_duplicates(['UID','day','time'],inplace=True)\n",
    "train_time_sub.sort_values(by=['UID','day','time'],inplace=True)\n",
    "\n",
    "train_time_sub['day_shift'] = train_time_sub.groupby('UID')['day'].shift(-1)\n",
    "train_time_sub['time_shift'] = train_time_sub.groupby(['UID', 'day'])['time'].shift(-1)\n",
    "\n",
    "train_time_sub = train_time_sub[['UID', 'day', 'time', 'day_shift', 'time_shift']]\n",
    "train_time_sub['day_sub'] = train_time_sub['day_shift'] - train_time_sub['day']\n",
    "train_time_sub['time_sub'] = (pd.to_datetime(train_time_sub.time_shift, format='%H:%M:%S')\n",
    "                              -pd.to_datetime(train_time_sub.time, format='%H:%M:%S')).dt.total_seconds()\n",
    "train_day_sub = train_time_sub.copy()\n",
    "train_day_sub['day_sub'] = train_time_sub['day_sub'].fillna(0)\n",
    "train_day_sub = train_day_sub.groupby('UID', as_index=False)['day_sub'].agg({'mean','max','min'}).add_prefix('day_sub_').reset_index()\n",
    "train_tst_feature = pd.merge(train_tst_feature, train_day_sub, on='UID', how='left')\n",
    "\n",
    "train_time_sub = train_time_sub[train_time_sub.time_sub.notnull()]\n",
    "\n",
    "train_time_sub = train_time_sub.groupby('UID', as_index=False)['time_sub'].agg({'mean','max','min'}).add_prefix('time_sub_').reset_index()\n",
    "train_tst_feature = pd.merge(train_tst_feature, train_time_sub, on='UID', how='left')\n",
    "train_tst_feature[['time_sub_mean', 'time_sub_min', 'time_sub_max']] = train_tst_feature[['time_sub_mean', \n",
    "                                                                                          'time_sub_min', 'time_sub_max']].fillna(0)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# 交易平台的个数，以及每个平台的交易金额的大小trans_amt的统计信息值"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array([102, 140, 119, 106], dtype=int64)"
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "train_tsts.channel.unique()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [],
   "source": [
    "'''交易平台的个数以及平均每天使用不同平台的个数'''\n",
    "train_channel_count = train_tsts.copy()\n",
    "train_channel_count.drop_duplicates(['UID', 'channel'], inplace=True)\n",
    "train_channel_count = train_channel_count.groupby('UID', as_index=False)['channel'].agg({'channel_count':'count'})\n",
    "train_tst_feature = pd.merge(train_tst_feature, train_channel_count, on='UID', how='left')\n",
    "\n",
    "'''平均每天使用的平台个数'''\n",
    "train_avg_channel = train_tsts.copy()\n",
    "train_avg_channel.drop_duplicates(['UID', 'day', 'channel'], inplace=True)\n",
    "train_avg_channel = train_avg_channel.groupby('UID', as_index=False)['channel'].agg({'channel_day_count':'count'})\n",
    "train_tst_feature = pd.merge(train_tst_feature, train_avg_channel, on='UID', how='left')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [],
   "source": [
    "'''每个平台的交易金额的统计信息值'''\n",
    "train_channel_amt = train_tsts.copy()\n",
    "train_channel_amt = train_channel_amt.groupby(['UID','channel'])['trans_amt'].agg({'mean','max','min'}).add_prefix('amt_channel_').unstack('channel')\n",
    "train_channel_amt.columns = [x[0]+\"_\"+str(x[1]) for x in train_channel_amt.columns.ravel()]\n",
    "train_channel_amt.fillna(0, inplace=True)\n",
    "train_channel_amt.reset_index(inplace=True)\n",
    "train_tst_feature = pd.merge(train_tst_feature, train_channel_amt, on='UID', how='left')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# 交易金额和余额的大小trans_amt，bal的统计分析"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>UID</th>\n",
       "      <th>channel</th>\n",
       "      <th>day</th>\n",
       "      <th>time</th>\n",
       "      <th>trans_amt</th>\n",
       "      <th>amt_src1</th>\n",
       "      <th>merchant</th>\n",
       "      <th>code1</th>\n",
       "      <th>code2</th>\n",
       "      <th>trans_type1</th>\n",
       "      <th>...</th>\n",
       "      <th>bal</th>\n",
       "      <th>amt_src2</th>\n",
       "      <th>acc_id2</th>\n",
       "      <th>acc_id3</th>\n",
       "      <th>geo_code</th>\n",
       "      <th>trans_type2</th>\n",
       "      <th>market_code</th>\n",
       "      <th>market_type</th>\n",
       "      <th>ip1_sub</th>\n",
       "      <th>Tag</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>0 rows × 28 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "Empty DataFrame\n",
       "Columns: [UID, channel, day, time, trans_amt, amt_src1, merchant, code1, code2, trans_type1, acc_id1, device_code1, device_code2, device_code3, device1, device2, mac1, ip1, bal, amt_src2, acc_id2, acc_id3, geo_code, trans_type2, market_code, market_type, ip1_sub, Tag]\n",
       "Index: []\n",
       "\n",
       "[0 rows x 28 columns]"
      ]
     },
     "execution_count": 20,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "train_tsts[train_tsts.bal.isnull()]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {},
   "outputs": [],
   "source": [
    "'''每个用户的钱的大小'''\n",
    "train_tst_feature = train_tst_feature.merge(train_tsts.groupby('UID', as_index=False)['trans_amt']\n",
    "                                            .agg({'trans_amt_all':'sum'}), on='UID', how='left')\n",
    "'''每个用户的余额大小'''\n",
    "train_tst_feature = train_tst_feature.merge(train_tsts.groupby('UID', as_index=False)['bal']\n",
    "                                            .agg({'bal_all':'sum'}), on='UID', how='left')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# 营销活动的相关特征，每天参与营销活动的类型，一共参与的营销活动，每个营销类型的金额相关特征"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {},
   "outputs": [],
   "source": [
    "'''平均每天参与的营销活动次数'''\n",
    "train_market_code = train_tsts.copy()\n",
    "train_market_code['market_code'].fillna(0, inplace=True)\n",
    "train_market_code.drop_duplicates(['UID','day', 'market_code'], inplace=True)\n",
    "train_market_code = train_market_code.groupby('UID', as_index=False)['market_code'].agg({'mark_code_day':'count'})\n",
    "train_tst_feature = pd.merge(train_tst_feature, train_market_code, on='UID', how='left')\n",
    "\n",
    "'''平均每天参与不同营销活动的次数'''\n",
    "train_market_code_diff = train_tsts.copy()\n",
    "train_market_code_diff['market_code'].fillna(0, inplace=True)\n",
    "train_market_code_diff.drop_duplicates(['UID', 'market_code'], inplace=True)\n",
    "train_market_code_diff = train_market_code_diff.groupby('UID', as_index=False)['market_code'].agg({'mark_code_count':'count'})\n",
    "train_tst_feature = pd.merge(train_tst_feature, train_market_code_diff, on='UID', how='left')\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "'''不同的营销类型的金额统计信息'''\n",
    "train_market_type = train_tsts.copy()\n",
    "train_market_type['market_type'].fillna(0, inplace=True)\n",
    "train_market_type = train_market_type.groupby(['UID','market_type'])['trans_amt'].agg({'mean','max','min'}).add_prefix('market_type_').unstack()\n",
    "train_market_type.columns = [x[0]+\"_\"+str(x[1]) for x in train_market_type.columns.ravel()]\n",
    "train_market_type.fillna(0, inplace=True)\n",
    "train_market_type.reset_index(inplace=True)\n",
    "train_tst_feature = pd.merge(train_tst_feature, train_market_type, on='UID', how='left')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# 交易类型的统计分析以及和交易金额的大小之间的关系信息"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "['26bcf43a19df14c8' 'c2f2023d279665b2' '6d55c54c8b1056fb'\n",
      " '61bfb66c928f36ac' 'e0d7b8768da99dd4' 'd9c417304a5ae70c'\n",
      " '9d7dd7b80e806024' '4adc3de71fe1a83c' 'a19e7a8951e54c06'\n",
      " 'ced62357ad496957' 'eb8d10591677bbe1' 'e903cf2a79b83d37'\n",
      " '85bced5214d33ad2' 'fd4d2d1006a95637' '3f469aa3836e71cb'\n",
      " '768160899ae359f6'] 16\n"
     ]
    }
   ],
   "source": [
    "print(train_tsts['trans_type1'].unique(), len(train_tsts['trans_type1'].unique()))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {},
   "outputs": [],
   "source": [
    "'''平均每天交易类型1,2的次数'''\n",
    "train_type_1 = train_tsts.copy()\n",
    "train_type_1.drop_duplicates(['UID', 'day', 'trans_type1'], inplace=True)\n",
    "train_type_1 = train_type_1.groupby('UID', as_index=False)['trans_type1'].agg({'trans_type1_count':'count'})\n",
    "train_tst_feature = pd.merge(train_tst_feature, train_type_1, on='UID', how='left')\n",
    "\n",
    "\n",
    "train_type_2 = train_tsts.copy()\n",
    "train_type_2.drop_duplicates(['UID', 'day', 'trans_type2'], inplace=True)\n",
    "train_type_2 = train_type_2.groupby('UID', as_index=False)['trans_type2'].agg({'trans_type2_count':'count'})\n",
    "train_tst_feature = pd.merge(train_tst_feature, train_type_2, on='UID', how='left')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "(424377, 28) 0.14847411617500478\n"
     ]
    }
   ],
   "source": [
    "print(train_tsts.shape, train_tsts[train_tsts.trans_type2.isnull()].shape[0]/train_tsts.shape[0])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>UID</th>\n",
       "      <th>channel</th>\n",
       "      <th>day</th>\n",
       "      <th>time</th>\n",
       "      <th>trans_amt</th>\n",
       "      <th>amt_src1</th>\n",
       "      <th>merchant</th>\n",
       "      <th>code1</th>\n",
       "      <th>code2</th>\n",
       "      <th>trans_type1</th>\n",
       "      <th>...</th>\n",
       "      <th>bal</th>\n",
       "      <th>amt_src2</th>\n",
       "      <th>acc_id2</th>\n",
       "      <th>acc_id3</th>\n",
       "      <th>geo_code</th>\n",
       "      <th>trans_type2</th>\n",
       "      <th>market_code</th>\n",
       "      <th>market_type</th>\n",
       "      <th>ip1_sub</th>\n",
       "      <th>Tag</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>3861</th>\n",
       "      <td>10007</td>\n",
       "      <td>140</td>\n",
       "      <td>18</td>\n",
       "      <td>10:58:19</td>\n",
       "      <td>157488</td>\n",
       "      <td>27c42480134c0d02</td>\n",
       "      <td>c71c876b8979028e</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>c2f2023d279665b2</td>\n",
       "      <td>...</td>\n",
       "      <td>100</td>\n",
       "      <td>e4cc53e1c547ea4e</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>ws4m</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>b139bf6618140c4a</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>48620</th>\n",
       "      <td>10007</td>\n",
       "      <td>140</td>\n",
       "      <td>18</td>\n",
       "      <td>00:48:26</td>\n",
       "      <td>5536</td>\n",
       "      <td>f29829bc82459191</td>\n",
       "      <td>c71c876b8979028e</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>c2f2023d279665b2</td>\n",
       "      <td>...</td>\n",
       "      <td>100</td>\n",
       "      <td>9a8ee16bde15e38a</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>ws4m</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>db6b37dff2c7047e</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>52062</th>\n",
       "      <td>10007</td>\n",
       "      <td>140</td>\n",
       "      <td>18</td>\n",
       "      <td>00:48:26</td>\n",
       "      <td>109918</td>\n",
       "      <td>27c42480134c0d02</td>\n",
       "      <td>c71c876b8979028e</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>c2f2023d279665b2</td>\n",
       "      <td>...</td>\n",
       "      <td>100</td>\n",
       "      <td>e4cc53e1c547ea4e</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>ws4m</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>db6b37dff2c7047e</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>64744</th>\n",
       "      <td>10007</td>\n",
       "      <td>140</td>\n",
       "      <td>18</td>\n",
       "      <td>10:58:19</td>\n",
       "      <td>5536</td>\n",
       "      <td>f29829bc82459191</td>\n",
       "      <td>c71c876b8979028e</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>c2f2023d279665b2</td>\n",
       "      <td>...</td>\n",
       "      <td>100</td>\n",
       "      <td>9a8ee16bde15e38a</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>ws4m</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>b139bf6618140c4a</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>4 rows × 28 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "         UID  channel  day      time  trans_amt          amt_src1  \\\n",
       "3861   10007      140   18  10:58:19     157488  27c42480134c0d02   \n",
       "48620  10007      140   18  00:48:26       5536  f29829bc82459191   \n",
       "52062  10007      140   18  00:48:26     109918  27c42480134c0d02   \n",
       "64744  10007      140   18  10:58:19       5536  f29829bc82459191   \n",
       "\n",
       "               merchant code1 code2       trans_type1 ...  bal  \\\n",
       "3861   c71c876b8979028e   NaN   NaN  c2f2023d279665b2 ...  100   \n",
       "48620  c71c876b8979028e   NaN   NaN  c2f2023d279665b2 ...  100   \n",
       "52062  c71c876b8979028e   NaN   NaN  c2f2023d279665b2 ...  100   \n",
       "64744  c71c876b8979028e   NaN   NaN  c2f2023d279665b2 ...  100   \n",
       "\n",
       "               amt_src2 acc_id2 acc_id3 geo_code trans_type2 market_code  \\\n",
       "3861   e4cc53e1c547ea4e     NaN     NaN     ws4m         NaN         NaN   \n",
       "48620  9a8ee16bde15e38a     NaN     NaN     ws4m         NaN         NaN   \n",
       "52062  e4cc53e1c547ea4e     NaN     NaN     ws4m         NaN         NaN   \n",
       "64744  9a8ee16bde15e38a     NaN     NaN     ws4m         NaN         NaN   \n",
       "\n",
       "      market_type           ip1_sub Tag  \n",
       "3861          NaN  b139bf6618140c4a   0  \n",
       "48620         NaN  db6b37dff2c7047e   0  \n",
       "52062         NaN  db6b37dff2c7047e   0  \n",
       "64744         NaN  b139bf6618140c4a   0  \n",
       "\n",
       "[4 rows x 28 columns]"
      ]
     },
     "execution_count": 27,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "train_tsts[train_tsts.UID == 10007]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "metadata": {},
   "outputs": [],
   "source": [
    "'''每个用户的交类型2的金额统计信息'''\n",
    "train_type2_loss = train_tsts.copy()\n",
    "train_type2_loss['trans_type2'].fillna(0, inplace=True)\n",
    "train_type2_loss = train_type2_loss.groupby(['UID'], as_index=False)['trans_type2'].agg({\n",
    "    'trans_type_loss':lambda x: x[x==0].count()/ x.count()})\n",
    "train_tst_feature = train_tst_feature.merge(train_type2_loss, on='UID', how='left')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# acc_id1分析"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "(424377, 28) 0.6146987230693464\n",
      "(424377, 28) 0.8876376429448344\n",
      "(424377, 28) 0.8861884597892912\n"
     ]
    }
   ],
   "source": [
    "print(train_tsts.shape, train_tsts[train_tsts.acc_id1.isnull()].shape[0]/train_tsts.shape[0])\n",
    "print(train_tsts.shape, train_tsts[train_tsts.acc_id2.isnull()].shape[0]/train_tsts.shape[0])\n",
    "print(train_tsts.shape, train_tsts[train_tsts.acc_id3.isnull()].shape[0]/train_tsts.shape[0])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "'''用户帐号相关accid1'''\n",
    "train_tst_accid1 = train_tsts.copy()\n",
    "train_tst_accid1.fillna(0, inplace=True)\n",
    "train_tst_feature = train_tst_feature.merge(train_tst_accid1.groupby('UID', as_index=False)['acc_id1'].agg({\n",
    "    'acc_id1_loss': lambda x: x[x==0].count() / x.count()\n",
    "}))\n",
    "train_tst_accid1.drop_duplicates(['UID','acc_id1'], inplace=True)\n",
    "train_tst_accid1 = train_tst_accid1.groupby('UID', as_index=False)['acc_id1'].agg({'acc_id1_count':'count'})\n",
    "train_tst_feature = train_tst_feature.merge(train_tst_accid1, on='UID', how='left')\n",
    "\n",
    "train_tst_accid1_day = train_tsts.copy()\n",
    "train_tst_accid1_day.fillna(0, inplace=True)\n",
    "train_tst_accid1_day.drop_duplicates(['UID','day','acc_id1'], inplace=True)\n",
    "train_tst_accid1_day = train_tst_accid1_day.groupby('UID', as_index=False)['acc_id1'].agg({'acc_id1_day':'count'})\n",
    "train_tst_feature = train_tst_feature.merge(train_tst_accid1_day, on='UID', how='left')\n",
    "\n",
    "\n",
    "\n",
    "'''用户帐号相关accid2'''\n",
    "train_tst_accid2 = train_tsts.copy()\n",
    "train_tst_accid2.fillna(0, inplace=True)\n",
    "train_tst_accid2.drop_duplicates(['UID','acc_id2'], inplace=True)\n",
    "train_tst_accid2 = train_tst_accid2.groupby('UID', as_index=False)['acc_id2'].agg({'acc_id2_count':'count'})\n",
    "train_tst_feature = train_tst_feature.merge(train_tst_accid2, on='UID', how='left')\n",
    "\n",
    "train_tst_accid2_day = train_tsts.copy()\n",
    "train_tst_accid2_day.fillna(0, inplace=True)\n",
    "train_tst_accid2_day.drop_duplicates(['UID','day','acc_id2'], inplace=True)\n",
    "train_tst_accid2_day = train_tst_accid2_day.groupby('UID', as_index=False)['acc_id2'].agg({'acc_id2_day':'count'})\n",
    "train_tst_feature = train_tst_feature.merge(train_tst_accid2_day, on='UID', how='left')\n",
    "\n",
    "'''用户帐号相关accid3'''\n",
    "train_tst_accid3 = train_tsts.copy()\n",
    "train_tst_accid3.fillna(0, inplace=True)\n",
    "train_tst_accid3.drop_duplicates(['UID','acc_id3'], inplace=True)\n",
    "train_tst_accid3 = train_tst_accid3.groupby('UID', as_index=False)['acc_id3'].agg({'acc_id3_count':'count'})\n",
    "train_tst_feature = train_tst_feature.merge(train_tst_accid3, on='UID', how='left')\n",
    "\n",
    "train_tst_accid3_day = train_tsts.copy()\n",
    "train_tst_accid3_day.fillna(0, inplace=True)\n",
    "train_tst_accid3_day.drop_duplicates(['UID','day','acc_id3'], inplace=True)\n",
    "train_tst_accid3_day = train_tst_accid3_day.groupby('UID', as_index=False)['acc_id3'].agg({'acc_id3_day':'count'})\n",
    "train_tst_feature = train_tst_feature.merge(train_tst_accid3_day, on='UID', how='left')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# 交易资金类型amt_src1"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "train_amt_src = train_tsts.copy()\n",
    "train_tst_feature = pd.merge(train_tst_feature, train_amt_src.groupby('UID', as_index=False)['amt_src1'].count())\n",
    "train_amt_src.drop_duplicates(['UID', 'day', 'amt_src1'], inplace=True)\n",
    "train_amt_src = train_amt_src.groupby('UID', as_index=False)['amt_src1'].agg({'amt_src_count':'count'})\n",
    "train_tst_feature = pd.merge(train_tst_feature, train_amt_src, on='UID', how='left')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# 商家的信息统计方法与分析，merchant, code1, code2"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "'''商家的个数，平均每天逛商家的个数'''\n",
    "train_merchant= train_tsts.copy()\n",
    "train_tst_feature = pd.merge(train_tst_feature, train_merchant.groupby('UID', as_index=False)['merchant'].count())\n",
    "train_merchant.drop_duplicates(['UID', 'day', 'merchant'], inplace=True)\n",
    "train_merchant = train_merchant.groupby('UID', as_index=False)['merchant'].agg({'merchant_count':'count'})\n",
    "train_tst_feature = pd.merge(train_tst_feature, train_merchant, on='UID', how='left')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "temp1 = train_tst.groupby(['UID', 'merchant'], as_index=False)['trans_amt'].agg({'sum', 'mean'}).add_prefix('merchant_').reset_index()\n",
    "temp1['trans_mer_number'] = temp1['merchant_sum'] / temp1['merchant_mean']\n",
    "temp1 = temp1.groupby('UID').agg({'mean','max', 'min','sum'})\n",
    "temp1.columns = [x[0]+'_'+x[1] for x in temp1.columns.ravel()]\n",
    "temp1.reset_index(inplace=True)\n",
    "train_tst_feature = pd.merge(train_tst_feature, temp1, on='UID', how='left')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "train_tst_feature.columns"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# 交易表中地理位置信息分析"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "train_tst_feature.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "'''平均每天变动的地理位置'''\n",
    "train_tst_geo = train_tsts.copy()\n",
    "train_tst_geo.fillna(0, inplace=True)\n",
    "train_tst_geo.drop_duplicates(['UID','geo_code'], inplace=True)\n",
    "train_tst_geo = train_tst_geo.groupby('UID', as_index=False)['geo_code'].agg({'geo_code_count':'count'})\n",
    "train_tst_feature = train_tst_feature.merge(train_tst_geo, on='UID', how='left')\n",
    "\n",
    "train_tst_geo_day = train_tsts.copy()\n",
    "train_tst_geo_day.geo_code.fillna(0)\n",
    "train_tst_geo_day = train_tst_geo_day.drop_duplicates(subset=['UID', 'day', 'geo_code'],keep='first')\n",
    "train_tst_geo_day = train_tst_geo_day.groupby(['UID'], as_index=False)['geo_code'].agg({'geo_code_day':'count'})\n",
    "train_tst_feature = pd.merge(train_tst_feature, train_tst_geo_day, on='UID', how='left')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# 交易表的设备特征提取"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "'''一个用户用了几个设置进行交易, 以及没多少次交易换设备，以及平均每天设备个数'''\n",
    "train_device = train_tsts.copy()\n",
    "train_device.device2 = train_device.device2.fillna(0)\n",
    "train_tst_feature = pd.merge(train_tst_feature, train_device.groupby('UID', as_index=False)['device2'].agg({'device_tst':'count'}))\n",
    "train_device.drop_duplicates(['UID','day','device2'], inplace=True)\n",
    "train_device = train_device.groupby(['UID'], as_index=False)['device2'].agg({'device_count_tst':'count'})\n",
    "train_tst_feature = pd.merge(train_tst_feature, train_device, on='UID', how='left')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# mac1地址的统计分析"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "'''统计用户mac地址的个数'''\n",
    "train_mac = train_tsts.copy()\n",
    "train_mac.mac1 = train_mac.mac1.fillna(0)\n",
    "\n",
    "train_mac.drop_duplicates(['UID','mac1'], inplace=True)\n",
    "train_mac = train_mac.groupby('UID', as_index=False)['mac1'].agg({'mac1_tst':'count'})\n",
    "train_tst_feature = train_tst_feature.merge(train_mac, on='UID', how='left')\n",
    "\n",
    "train_mac_day = train_tsts.copy()\n",
    "train_mac_day.mac1.fillna(0, inplace=True)\n",
    "train_mac_day.drop_duplicates(['UID','day','mac1'], inplace=True)\n",
    "train_mac_day = train_mac_day.groupby('UID', as_index=False)['mac1'].agg({'mac1_day':'count'})\n",
    "train_tst_feature = train_tst_feature.merge(train_mac_day, on='UID', how='left')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# 交易表中交易是否为苹果手机"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "train_apple = train_tsts.copy()\n",
    "train_apple.device_code3 = train_apple.device_code3.fillna(0)\n",
    "train_apple.device_code1 = train_apple.device_code1.fillna(0)\n",
    "\n",
    "train_apple.device_code3= train_apple.device_code3.apply(lambda x: x if x == 0 else 1)\n",
    "train_apple.device_code1 = train_apple.device_code1.apply(lambda x: x if x == 0 else 1)\n",
    "\n",
    "train_apple = train_apple.groupby('UID', as_index=False)[['device_code1', 'device_code3']].sum()\n",
    "train_apple['is_apple_tst'] = np.where(train_apple.device_code1 > train_apple.device_code3, 0,1)\n",
    "train_apple['is_apple_an_tst'] = np.where((train_apple.device_code1!=0)&(train_apple.device_code3!=0),1,0)\n",
    "train_apple.drop(['device_code1', 'device_code3'], axis=1, inplace=True)\n",
    "train_tst_feature = pd.merge(train_tst_feature, train_apple, on='UID', how='left')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# ip地址信息的相关统计分析ip1,ip1_sub"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "'''平均每天换多少个ip'''\n",
    "train_tst_ip = train_tsts.copy()\n",
    "train_tst_ip['ip1'] = train_tst_ip['ip1'].fillna(0)\n",
    "train_tst_feature = train_tst_feature.merge(train_tst_ip.groupby('UID', as_index=False)['ip1'].agg({\n",
    "    'ip1_loss': lambda x: x[x==0].count() / x.count()\n",
    "}))\n",
    "\n",
    "train_tst_ip.drop_duplicates(['UID', 'day', 'ip1'], inplace=True)\n",
    "train_tst_ip = train_tst_ip.groupby('UID', as_index=False)['ip1'].agg({'ip1_day_tst':'count'})\n",
    "train_tst_feature = pd.merge(train_tst_feature, train_tst_ip, on='UID', how='left')\n",
    "\n",
    "'''每多少次交易换一次ip'''\n",
    "train_ip_trans = train_tsts.copy()\n",
    "train_ip_trans['ip1'] = train_ip_trans['ip1'].fillna(0)\n",
    "train_ip_trans = train_ip_trans.groupby('UID', as_index=False)['ip1'].agg({'ip1_count_tst':'count'})\n",
    "train_tst_feature = pd.merge(train_tst_feature, train_ip_trans, on='UID', how='left')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# 平均化特征处理"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "train_tst_feature.columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "cols1 = ['channel_count', 'channel_day_count', 'amt_channel_mean_102',\n",
    "       'amt_channel_mean_106', 'amt_channel_mean_118', 'amt_channel_mean_119',\n",
    "    'trans_amt_all','bal_all', 'mark_code_day', 'mark_code_count', 'market_type_mean_0.0',\n",
    "    'market_type_mean_1.0', 'market_type_mean_2.0','trans_type2_count','acc_id1_count',\n",
    "       'acc_id1_day', 'acc_id2_count', 'acc_id2_day', 'acc_id3_count',\n",
    "       'acc_id3_day', 'amt_src1', 'amt_src_count', 'merchant', 'merchant_count',\n",
    "        'merchant_mean_mean', 'merchant_mean_sum',\n",
    "       'merchant_mean_max', 'merchant_mean_min', 'merchant_sum_mean',\n",
    "       'merchant_sum_sum', 'merchant_sum_max', 'merchant_sum_min',\n",
    "       'trans_mer_number_mean', 'trans_mer_number_sum', 'trans_mer_number_max',\n",
    "       'trans_mer_number_min', 'geo_code_count', 'geo_code_day', 'device_tst',\n",
    "       'device_count_tst', 'mac1_tst', 'mac1_day','ip1_day_tst', 'ip1_count_tst'\n",
    "]\n",
    "for i in cols1:\n",
    "    train_tst_feature['avg_trans_{}'.format(i)] = train_tst_feature[i] / train_tst_feature['trans_count']\n",
    "    train_tst_feature['avg_trans_day_{}'.format(i)] = train_tst_feature[i] / train_tst_feature['day_count']\n",
    "    \n",
    "train_tst_feature['avg_trans_mat_bal'] = train_tst_feature['trans_amt_all'] / train_tst_feature['bal_all']\n",
    "\n",
    "cols2 = ['channel_count', 'channel_day_count','mark_code_day', 'mark_code_count',\n",
    "        'trans_type1_count','amt_src1', 'amt_src_count', 'merchant',\n",
    "       'merchant_count','device_tst','device_count_tst','trans_type2_count']\n",
    "\n",
    "for j in cols2:\n",
    "    train_tst_feature['avg_trans_amt_{}'.format(i)] = train_tst_feature['trans_amt_all'] / train_tst_feature[i]\n",
    "    train_tst_feature['avg_trans_bal_{}'.format(i)] = train_tst_feature['bal_all'] / train_tst_feature[i]\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "train_tst_feature.shape"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# 特征的保存"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "# train_tst_feature.to_csv('./train_tst_feature.csv', index=False, encoding='utf-8')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.6.5"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}
                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                     rans_mer_number_max  \n",
       "0                   1.0               2.714286                   9.0  \n",
       "1                   2.0               6.333333                  15.0  \n",
       "2                   1.0               1.336088                  29.0  \n",
       "3                   6.0               6.000000                   6.0  \n",
       "4                   2.0               2.333333                   4.0  \n",
       "\n",
       "[5 rows x 85 columns]"
      ]
     },
     "execution_count": 83,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "train_tst_feature.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "print(len(set(([str(i)[0:2] for i in train_tsts.geo_code.unique()]))))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "'''平均每天变动的地理位置'''\n",
    "train_tst_geo = train_tsts.copy()\n",
    "train_tst_geo.fillna(0, inplace=True)\n",
    "train_tst_geo.drop_duplicates(['UID','geo_code'], inplace=True)\n",
    "train_tst_geo = train_tst_geo.groupby('UID', as_index=False)['geo_code'].agg({'geo_code_count':'count'})\n",
    "train_tst_feature = train_tst_feature.merge(train_tst_geo, on='UID', how='left')\n",
    "\n",
    "train_tst_geo_day = train_tsts.copy()\n",
    "train_tst_geo_day.geo_code.fillna(0)\n",
    "train_tst_geo_day = train_tst_geo_day.drop_duplicates(subset=['UID', 'day', 'geo_code'],keep='first')\n",
    "train_tst_geo_day = train_tst_geo_day.groupby(['UID'], as_index=False)['geo_code'].agg({'geo_code_day':'count'})\n",
    "train_tst_feature = pd.merge(train_tst_feature, train_tst_geo_day, on='UID', how='left')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# 交易表的设备特征提取"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "'''一个用户用了几个设置进行交易, 以及没多少次交易换设备，以及平均每天设备个数'''\n",
    "train_device = train_tsts.copy()\n",
    "train_device.device2 = train_device.device2.fillna(0)\n",
    "train_tst_feature = pd.merge(train_tst_feature, train_device.groupby('UID', as_index=False)['device2'].agg({'device_tst':'count'}))\n",
    "train_device.drop_duplicates(['UID','day','device2'], inplace=True)\n",
    "train_device = train_device.groupby(['UID'], as_index=False)['device2'].agg({'device_count_tst':'count'})\n",
    "train_tst_feature = pd.merge(train_tst_feature, train_device, on='UID', how='left')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# mac1地址的统计分析"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "'''统计用户mac地址的个数'''\n",
    "train_mac = train_tsts.copy()\n",
    "train_mac.mac1 = train_mac.mac1.fillna(0)\n",
    "\n",
    "train_mac.drop_duplicates(['UID','mac1'], inplace=True)\n",
    "train_mac = train_mac.groupby('UID', as_index=False)['mac1'].agg({'mac1_tst':'count'})\n",
    "train_tst_feature = train_tst_feature.merge(train_mac, on='UID', how='left')\n",
    "\n",
    "train_mac_day = train_tsts.copy()\n",
    "train_mac_day.mac1.fillna(0, inplace=True)\n",
    "train_mac_day.drop_duplicates(['UID','day','mac1'], inplace=True)\n",
    "train_mac_day = train_mac_day.groupby('UID', as_index=False)['mac1'].agg({'mac1_day':'count'})\n",
    "train_tst_feature = train_tst_feature.merge(train_mac_day, on='UID', how='left')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# 交易表中交易是否为苹果手机"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "train_apple = train_tsts.copy()\n",
    "train_apple.device_code3 = train_apple.device_code3.fillna(0)\n",
    "train_apple.device_code1 = train_apple.device_code1.fillna(0)\n",
    "\n",
    "train_apple.device_code3= train_apple.device_code3.apply(lambda x: x if x == 0 else 1)\n",
    "train_apple.device_code1 = train_apple.device_code1.apply(lambda x: x if x == 0 else 1)\n",
    "\n",
    "train_apple = train_apple.groupby('UID', as_index=False)[['device_code1', 'device_code3']].sum()\n",
    "train_apple['is_apple_tst'] = np.where(train_apple.device_code1 > train_apple.device_code3, 0,1)\n",
    "train_apple['is_apple_an_tst'] = np.where((train_apple.device_code1!=0)&(train_apple.device_code3!=0),1,0)\n",
    "train_apple.drop(['device_code1', 'device_code3'], axis=1, inplace=True)\n",
    "train_tst_feature = pd.merge(train_tst_feature, train_apple, on='UID', how='left')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# ip地址信息的相关统计分析ip1,ip1_sub"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "'''平均每天换多少个ip'''\n",
    "train_tst_ip = train_tsts.copy()\n",
    "train_tst_ip['ip1'] = train_tst_ip['ip1'].fillna(0)\n",
    "train_tst_feature = train_tst_feature.merge(train_tst_ip.groupby('UID', as_index=False)['ip1'].agg({\n",
    "    'ip1_loss': lambda x: x[x==0].count() / x.count()\n",
    "}))\n",
    "\n",
    "train_tst_ip.drop_duplicates(['UID', 'day', 'ip1'], inplace=True)\n",
    "train_tst_ip = train_tst_ip.groupby('UID', as_index=False)['ip1'].agg({'ip1_day_tst':'count'})\n",
    "train_tst_feature = pd.merge(train_tst_feature, train_tst_ip, on='UID', how='left')\n",
    "\n",
    "'''每多少次交易换一次ip'''\n",
    "train_ip_trans = train_tsts.copy()\n",
    "train_ip_trans['ip1'] = train_ip_trans['ip1'].fillna(0)\n",
    "train_ip_trans = train_ip_trans.groupby('UID', as_index=False)['ip1'].agg({'ip1_count_tst':'count'})\n",
    "train_tst_feature = pd.merge(train_tst_feature, train_ip_trans, on='UID', how='left')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# 平均化特征处理"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "train_tst_feature.columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "cols1 = ['channel_count', 'channel_day_count', 'amt_channel_mean_102',\n",
    "       'amt_channel_mean_106', 'amt_channel_mean_118', 'amt_channel_mean_119',\n",
    "    'trans_amt_all','bal_all', 'mark_code_day', 'mark_code_count', 'market_type_mean_0.0',\n",
    "    'market_type_mean_1.0', 'market_type_mean_2.0','trans_type2_count','acc_id1_count',\n",
    "       'acc_id1_day', 'acc_id2_count', 'acc_id2_day', 'acc_id3_count',\n",
    "       'acc_id3_day', 'amt_src1', 'amt_src_count', 'merchant', 'merchant_count',\n",
    "        'merchant_mean_mean', 'merchant_mean_sum',\n",
    "       'merchant_mean_max', 'merchant_mean_min', 'merchant_sum_mean',\n",
    "       'merchant_sum_sum', 'merchant_sum_max', 'merchant_sum_min',\n",
    "       'trans_mer_number_mean', 'trans_mer_number_sum', 'trans_mer_number_max',\n",
    "       'trans_mer_number_min', 'geo_code_count', 'geo_code_day', 'device_tst',\n",
    "       'device_count_tst', 'mac1_tst', 'mac1_day','ip1_day_tst', 'ip1_count_tst'\n",
    "]\n",
    "for i in cols1:\n",
    "    train_tst_feature['avg_trans_{}'.format(i)] = train_tst_feature[i] / train_tst_feature['trans_count']\n",
    "    train_tst_feature['avg_trans_day_{}'.format(i)] = train_tst_feature[i] / train_tst_feature['day_count']\n",
    "    \n",
    "train_tst_feature['avg_trans_mat_bal'] = train_tst_feature['trans_amt_all'] / train_tst_feature['bal_all']\n",
    "\n",
    "cols2 = ['channel_count', 'channel_day_count','mark_code_day', 'mark_code_count',\n",
    "        'trans_type1_count','amt_src1', 'amt_src_count', 'merchant',\n",
    "       'merchant_count','device_tst','device_count_tst','trans_type2_count']\n",
    "\n",
    "for j in cols2:\n",
    "    train_tst_feature['avg_trans_amt_{}'.format(i)] = train_tst_feature['trans_amt_all'] / train_tst_feature[i]\n",
    "    train_tst_feature['avg_trans_bal_{}'.format(i)] = train_tst_feature['bal_all'] / train_tst_feature[i]\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "train_tst_feature.shape"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# 特征的保存"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "# train_tst_feature.to_csv('./train_tst_feature.csv', index=False, encoding='utf-8')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.6.5"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}
                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                              {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "{14529}"
      ]
     },
     "execution_count": 63,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "set(train_op_tr.UID.unique()) - set(train_op_feature[train_op_feature.Tag != -1].UID)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.6.5"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}
                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                              
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.6.5"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}
                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                .drop_duplicates(['UID','mac1'], inplace=True)\n",
    "train_mac = train_mac.groupby('UID', as_index=False)['mac1'].agg({'mac1_tst':'count'})\n",
    "train_tst_feature = train_tst_feature.merge(train_mac, on='UID', how='left')\n",
    "\n",
    "train_mac_day = train_tsts.copy()\n",
    "train_mac_day.mac1.fillna(0, inplace=True)\n",
    "train_mac_day.drop_duplicates(['UID','day','mac1'], inplace=True)\n",
    "train_mac_day = train_mac_day.groupby('UID', as_index=False)['mac1'].agg({'mac1_day':'count'})\n",
    "train_tst_feature = train_tst_feature.merge(train_mac_day, on='UID', how='left')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# 交易表中交易是否为苹果手机"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 80,
   "metadata": {},
   "outputs": [],
   "source": [
    "train_apple = train_tsts.copy()\n",
    "train_apple.device_code3 = train_apple.device_code3.fillna(0)\n",
    "train_apple.device_code1 = train_apple.device_code1.fillna(0)\n",
    "\n",
    "train_apple.device_code3= train_apple.device_code3.apply(lambda x: x if x == 0 else 1)\n",
    "train_apple.device_code1 = train_apple.device_code1.apply(lambda x: x if x == 0 else 1)\n",
    "\n",
    "train_apple = train_apple.groupby('UID', as_index=False)[['device_code1', 'device_code3']].sum()\n",
    "train_apple['is_apple_tst'] = np.where(train_apple.device_code1 > train_apple.device_code3, 0,1)\n",
    "train_apple['is_apple_an_tst'] = np.where((train_apple.device_code1!=0)&(train_apple.device_code3!=0),1,0)\n",
    "train_apple.drop(['device_code1', 'device_code3'], axis=1, inplace=True)\n",
    "train_tst_feature = pd.merge(train_tst_feature, train_apple, on='UID', how='left')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# ip地址信息的相关统计分析ip1,ip1_sub"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 81,
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "'''平均每天换多少个ip'''\n",
    "train_tst_ip = train_tsts.copy()\n",
    "train_tst_ip['ip1'] = train_tst_ip['ip1'].fillna(0)\n",
    "train_tst_feature = train_tst_feature.merge(train_tst_ip.groupby('UID', as_